Menu
Ask a question Report

Format fill colour based on another cell [Solved/Closed]

Posts
2
Registration date
Monday June 30, 2014
Last seen
July 2, 2014
 - - Latest reply: EndlessDread
Posts
2
Registration date
Monday June 30, 2014
Last seen
July 2, 2014
 - Jul 2, 2014 at 02:01 PM
Excel Ask a question Report
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
See more 

1 reply

Report Respond to TrowaD
Posts
2440
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 13, 2018
0
Thank you
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.
Report
EndlessDread
Posts
2
Registration date
Monday June 30, 2014
Last seen
July 2, 2014
 -
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
Ask a question