Change color for cells in a row that are the same as the cell in a row above

Report
-
ac3mark
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
-
Hello,
how do I ensure that the VB code changes the colour of cells that are right above it?
Sub ChangeColor()
lRow = Range("C3:I3").End(xlUp).Row
Set MR = Range("C2,D3,F3,G3,H3,I3")
For Each cell In MR
If cell.Value = " Yes" Then cell.Interior.ColorIndex = 10
If cell.Value = "No" Then cell.Interior.ColorIndex = 3
Next
End Sub

1 reply

Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,668
If you are wanting to change the cell above it, you must keep track of the row, and subtract one from it!