Chase cell color for every other variable

[crazyeyeschase]

I have a spreadsheet where i input PO information for multiple buildings. Some of these PO's have multiple line items which also get included in the sheet.

I am wanting to change the cell color of every other PO to help define each PO. Im not sure if conditional formatting would be best or a macro.

Something similar to below.

PO

12345678

12345678

12345679

12345680

12345681

I was thinking the following but need some guidance if I'm heading in the right direction before writing the code.

For each cell in A to last row
If cell value = offset down 1 value then
     if cell.interior color =  white then
          select range of cell and offset down 1 to column J and change interior color to white
     else if cell.interior color = gray then
           select range of cell and offset down 1 to column J and change interior color to gray
     end if
elseif cell value <> offset down 1 value then
    if cell.interior color =  white then
          select range of offset down 1 to column J and change interior color to gray
     else if cell.interior color = gray then
           select range of offset down 1 to column J and change interior color to white
     end if
end if
next c

If i have this correct this will check to see if the values are equal and if so it will then check to see the active cells interior color and change the offset cell and row to match

If the cells are not it will then determine the active cells color and change the offset to the opposite. to

 

[Joe4]

I believe the issue is with your IF statement. The default ColorIndex Excel uses is -4142, not 2.
You can see that by selecting any cell and running this VBA code:

Sub check()
    MsgBox ActiveCell.Interior.ColorIndex
End Sub

which will return the ColorIndex value of that cell to a MsgBox, i.e.

So I don't think this part of your code is doing that you want:

If C.Interior.ColorIndex = 2 Then

 

[Joe4]

Now that I see how you want this to work, I re-wrote the code for you like this:

Sub highlight_cell()
    Dim LR As Long
    Dim C As Range
    Dim CX1 As Long, CX2 As Long

    '15 gray
    '-4142 white
    
    Application.ScreenUpdating = False
    
    LR = Cells(Rows.Count, 1).End(xlUp).Row

    For Each C In Range("A2:A" & LR)
        'Skip first row
        If C.Row > 2 Then
'           Get color index from row above
            CX1 = C.Offset(-1, 0).Interior.ColorIndex
        '   If row is same value as above, set color index to be same as row above
            If C.Value = C.Offset(-1, 0).Value Then
                CX2 = CX1
            Else
'               Set color opposite from cell above
                Select Case CX1
                    Case -4142
                        CX2 = 15
                    Case 15
                        CX2 = -4142
                End Select
            End If
'           Color row of cells
            Range(C.Offset(0, 0), C.Offset(0, 11)).Interior.ColorIndex = CX2
        End If
    Next C
    
    Application.ScreenUpdating = True
    
End Sub

When I run it, it returns this:

 

[crazyeyeschase]

Now that I see how you want this to work, I re-wrote the code for you like this:

Sub highlight_cell()
    Dim LR As Long
    Dim C As Range
    Dim CX1 As Long, CX2 As Long

    '15 gray
    '-4142 white
   
    Application.ScreenUpdating = False
   
    LR = Cells(Rows.Count, 1).End(xlUp).Row

    For Each C In Range("A2:A" & LR)
        'Skip first row
        If C.Row > 2 Then
'           Get color index from row above
            CX1 = C.Offset(-1, 0).Interior.ColorIndex
        '   If row is same value as above, set color index to be same as row above
            If C.Value = C.Offset(-1, 0).Value Then
                CX2 = CX1
            Else
'               Set color opposite from cell above
                Select Case CX1
                    Case -4142
                        CX2 = 15
                    Case 15
                        CX2 = -4142
                End Select
            End If
'           Color row of cells
            Range(C.Offset(0, 0), C.Offset(0, 11)).Interior.ColorIndex = CX2
        End If
    Next C
   
    Application.ScreenUpdating = True
   
End Sub

When I run it, it returns this:
View attachment 102756

Hey thanks so much, I did notice the color code was coming up at -4142 but multiple sites I found while researching shows the color index for white as 2.. I had tested it ona few areas and seemed to work but either way im happy.

Thanks for the help.

Ill read thru your revised code and learn from my mistakes.

 

[Joe4]

Hey thanks so much, I did notice the color code was coming up at -4142 but multiple sites I found while researching shows the color index for white as 2.. I had tested it ona few areas and seemed to work but either way im happy.

Thanks for the help.

Ill read thru your revised code and learn from my mistakes.

You are welcome.

There can be multiple different number codes for the same color, and it may not be evident which code Excel recognizes. You can always use that code "trick" I showed you to have Excel return the color code on any cell.