Format fill colour based on another cellSolved/Closed

Question

Hi
I would like to format my excel page so that the rows alternate between two fill colors based on whether two cells are equal or not. Here is an example of what I would like it to look like.

I would like it so that the color of the row depends on the value of the A column of that row and the row above it.
For example:
if A5 is does not equal A4 then Row 5 is a different color than Row 4
if A5 is equal to A4 the Row 5 is the same color as Row 4
but I want all the rows to only alternate between two colors

I'm using Excel 2013 and I would appreciate any and all help

TrowaD · Answer

Hi EndlessDread,

Your explanation doesn't match your pic. I assumed that the A column had to be the F column.

Give this code a try:
Sub RunMe()
Dim lRow As Long
lRow = Range("F1").End(xlDown).Row

Range("A1:F1").Interior.ColorIndex = 5

For Each cell In Range("F2:F" & lRow)
    If cell.Value = cell.Offset(-1, 0).Value Then
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Interior.ColorIndex = cell.Offset(-1, 0).Interior.ColorIndex
    Else
        If cell.Offset(-1, 0).Interior.ColorIndex = 5 Then
            Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Interior.ColorIndex = 8
        Else
            Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Interior.ColorIndex = 5
        End If
    End If
Next cell

End Sub

Best regards,
Trowa 
 
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.

Format fill colour based on another cell

1 response

Similar discussions

Subscribe To Our Newsletter!