Previous Topic Next Topic

Format fill colour based on another cell

Solved/Closed
EndlessDread Posts 2 Registration date Monday June 30, 2014 Status Member Last seen July 2, 2014 - Jun 30, 2014 at 03:10 PM
EndlessDread Posts 2 Registration date Monday June 30, 2014 Status Member Last seen July 2, 2014 - Jul 2, 2014 at 02:01 PM
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
Related:

1 reply

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Jul 1, 2014 at 11:40 AM
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.
0
EndlessDread Posts 2 Registration date Monday June 30, 2014 Status Member Last seen July 2, 2014
Jul 2, 2014 at 02:01 PM
Thank you so much. This code works perfectly and is a great help in making my excel file more organized and easier to read.

And I'm sorry about the image, my excel file is complicated and in my attempt to simplify and cut it for this image I accidentally change the values. With only a minor change the code you gave me worked fine.

Thank you again
0