Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Report
Posts
2
Registration date
Monday June 30, 2014
Status
Member
Last seen
July 2, 2014
 -
Posts
2
Registration date
Monday June 30, 2014
Status
Member
Last seen
July 2, 2014
 -
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
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 458
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
Posts
2
Registration date
Monday June 30, 2014
Status
Member
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!