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.
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.
Open VB editor from Developer tab in excel as shown in the figure.
Insert a module in which we will write a VBA code to color excel cells conditionally.
Now double click the newly created module to open that and paste the below code in the module.
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
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.
Hope this helped.