Format fill colour based on another cell [Solved/Closed]
EndlessDread2
Posts Monday June 30, 2014Registration date
July 2, 2014
Last seen
-
Jun 30, 2014 at 03:10 PM - Latest reply: EndlessDread2
Posts Monday June 30, 2014Registration date
July 2, 2014
Last seen
- 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
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.
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.
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