How to Add Color to Specific Cells via Excel VBA

Excel is the top popular software in many companies. And the function of batch processing is one of the most notable features. What’s more, VBA is the best tool for batch processing. And today let’s see the VBA code of adding color to special cells.

VBA is a kind of macro language in Excel. And in handling with large number of data and information, using this language can be more convenient. Only a few lines of VBA code have the ability of helping you achieve a lot of functions. In an Excel file, you will probably need to highlight some cell. And the marks can increase the readability of the Excel file. And the following is an example of adding color to certain cells using the VBA code.

A Sample Worksheet of Student Scores

This is a worksheet of students’ scores.A Sample File for The Excel tips

Now, you need to mark red of the scores which are below 60. Since there are many scores, it is impossible for you do mark the scores one by one. And now you can use the following VBA code to finish this necessity.

Steps of Using VBA Code

  1. Press the key “Alt + F11”. Then you will see a new pop-up window. This is the interface of VBA.
  2. Double click “sheet1” on the left of the interface. And then you will see a new window.Double Click Sheet1
  3. Copy the following code:

Sub SetColor()

Dim nRow As Integer
Dim nColumn As Integer

For nRow = 1 To Worksheets(“StudentScoreSheet”).UsedRange.Rows.Count

For nColumn = 1 To Worksheets(“StudentScoreSheet”).UsedRange.Columns.Count

If Worksheets(“StudentScoreSheet”).Cells(nRow, nColumn).Value < 60 Then

Worksheets(“StudentScoreSheet”).Cells(nRow, nColumn).Font.Color = RGB(255, 0, 0)

End If

Next nColumn

Next nRow

End Sub

  1. And then paste this code to the window that pops up in Step 2.Paste the Code to the Window
  2. Click the button of “Run”.Click the Button of Run
  3. Close the window of VBA, so you come back to the window of Excel. And then you will find that all the scores below 60 become red.The Scores Become Red

This is because in the VBA code, the scores below 60 can be identified automatically. And once the system selects the scores, it will mark red of those numbers. Using this function and the VBA code can literally save a lot of time and energy.

Solution to Excel Corruption

Therefore, the combination of VBA and Excel enable Excel to be the most popular office software. And many people have applied this software in their daily work. However, some accidents will occur inevitably. And maybe those accidents will cause damage to your Excel files. In addition, consequences such as data loss, file corrupted may happen. You can also use the Excel recovery software to repair your damaged files.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com

One response to “How to Add Color to Specific Cells via Excel VBA”