Color if color..

[emukiss10]

Hello,

simple one

if color of any cell in range lastRow col A, columns A:P is not white than color every white cells in this row in this range to RGB(157,157,157).


I'd like to color every white cell from column A to column P to grey but do not touch red colored cells only if red cell(s) is(are) found..

for every cell in range
if any cell in range <> rgb(255,255,255) than
for every cell = rgb(255,255,255) in range
Cells(?,?).Interior.Color = RGB(157, 157, 157)

something like that..

Best regards
W.

 

[Scott Huish]

Perhaps:

Sub test()
Dim c As Range
For Each c In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If c.Interior.Color <> vbWhite And c.Interior.Color <> vbRed Then
        Cells(c.Row, "P") = c
        Cells(c.Row, "P").Interior.Color = RGB(157, 157, 157)
    End If
Next
End Sub

 

[emukiss10]

It does nothing..

Ive chceked it on new wb and it does nothing. Something is wrong.

if any cell in range A:P is not white then it should colour every white cell in the same range.

 

[emukiss10]

Mayby I wosn't clear.

I have data sheet filled from column A to column P
some of the cells in whole sheet has interior color.
I need to search entire WB for colored cells and if non white cell is found then color row from A to P to gray BUT do not color non white cells.

 

[emukiss10]

ok, Ive done it. Little around but it works. Little shorter range but concept is the same.

Please remove my provious posts (we do not like spam) or the whole thread if its not helpful.

  Dim LstRw2 As Long, ThsRw2 As Long
        
        LstRw2 = Cells(Rows.count, "A").End(xlUp).row
        
        For ThsRw2 = 2 To LstRw2
                If Cells(ThsRw2, "B").Interior.Color <> RGB(255, 255, 255) Or _
                Cells(ThsRw2, "C").Interior.Color <> RGB(255, 255, 255) Or _
                Cells(ThsRw2, "D").Interior.Color <> RGB(255, 255, 255) Or _
                Cells(ThsRw2, "E").Interior.Color <> RGB(255, 255, 255) Or _
                Cells(ThsRw2, "F").Interior.Color <> RGB(255, 255, 255) Or _
                Cells(ThsRw2, "G").Interior.Color <> RGB(255, 255, 255) Or _
                Cells(ThsRw2, "H").Interior.Color <> RGB(255, 255, 255) Or _
                Cells(ThsRw2, "I").Interior.Color <> RGB(255, 255, 255) Or _
                Cells(ThsRw2, "J").Interior.Color <> RGB(255, 255, 255) Or _
                Cells(ThsRw2, "K").Interior.Color <> RGB(255, 255, 255) Or _
                Cells(ThsRw2, "L").Interior.Color <> RGB(255, 255, 255) _
                Then
                Cells(ThsRw2, "A").Interior.Color = RGB(157, 157, 157)
                End If
        Next ThsRw2

Scott Huish Thank You for Your Help.

Best Regards!
W.

 

[Scott Huish]

That doesn't address your red exception.

If you no longer want that, my code can be changed to this. Looping through the range eliminates the need for all those Or statements.

Sub test()
Dim c As Range
For Each c In Range("B2:L" & Range("A" & Rows.Count).End(xlUp).Row)
    If c.Interior.Color <> vbWhite Then c.Interior.Color = RGB(157, 157, 157)
Next
End Sub

 

[emukiss10]

Hello, Sorry for the delay. I was ill and out of office. Can you modify these codes a little?
If you find non white cells in row, color every white cell to RGB(157, 157, 157) in range A:L of that row.

I mean, I need to mark rows with errors (non white cells in my case) by coloring grey other cells in that row. But not entire row. Just in range A:L

Im trying to refactor it myself but it's too complicated for me.