Color Excel Cell Based On A Different Cell Value

In his post we will see how to color cell based on a different cell value, we will color excel cell conditionally based on some condition which will leverage values in some other cell.

For example in the below data we will color excel cell in column A based on the condition that we will calculate from the values that we have in column B.

Conditional formatting can give you a range between 2 colors, or even 3 colors but when you want 4 or more it’s better to write a VBA code to change the cell color based on another cell value.




Excel showing cells that need to be colored on the basis of value from another cell

We will color based on the number that we have I column B, for example if we have 1 in column B then corresponding row in column A will be red.

Step 1

Open VB editor from Developer tab in excel as shown in the figure.

Open developer tab to write VBA to loop through each cell and color them conditionally

Step 2

Insert a module in which we will write a VBA code to color excel cells conditionally.

Insert a module in the VB editor

Step 3

Now double click the newly created module to open that and paste the below code in the module.




Sub Color_Cell()

   Dim i As Long, r1 As Range, r2 As Range

   For i = 2 To 5

      Set r1 = Range(“A” & i)

      Set r2 = Range(“B” & i)

      If r2.Value = 1 Then r1.Interior.Color = vbRed

      If r2.Value = 2 Then r1.Interior.Color = vbGreen

   Next i

End Sub

VBA code to color cells based on different cell value

This program is looping through rows in column A and coloring them based on the condition i.e. red if column B has 1 and green if B has 2.

Now run the program by pressing F5 or by pressing the play button in VB editor.

We can see the result in the sheet that vba program has changed the color of the cell based on values in a different cell.

cells are colored on the basis of values from another cells

Hope this helped.




Share The Knowledge

Random Posts