Conditional formatting cells that are NOT "Cell Highlighted"

[SemiConGuy]

I created an excel spreadsheet with a conditional format of =MOD(ROW(),2)=1 which sets the fill color on alternating rows to allow for easier horizontal reading. The problem is that if the cell is "shaded" by this conditional format, I cannot manually set the fill color (example, to show certain cells as RED to indicate an issue, etc...) Anyone have any ideas?

A confused Semiconductors Guy.

 

[onlyadrafter]

Hello,

How about adding another conditional formatting? The existing one will have to after the new onw though.

Does this help?

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[joerd]

or you'll just have to do it the old way and apply the fill color on alternating rows manually :-(
This can be done quickly by just formatting 2 rows, and then copy them down as far as you like.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[SemiConGuy]

I really like the idea of creating another conditional format. I tried to use the cell function to determine if the cell was manually formatted (ie, filled) then do NOT format. Unfortunately, the cell function does not return if the cell is "filled". Perhaps there is another way to determine if a cell is filled / determine the cell attributes?

Eric
Semiconductors Guy

 

[kris44]

Use VBA. This has 2 advantages
[ol]
[li]Allows for easy modification[/li]
[li]Reduces the size of the file by eliminating conditional formatting[/li]
[/ol]

Sub macro1()

For Row = 1 To 65536
        For Col = 1 To 256
            ActiveSheet.Cells(2 * Row - 1, Col).Interior.Color = RGB(0, 256, 256)
        Next
    
Next

End Sub

Of course if you don't want to do the entire worksheet then adjust the number of rows and columns accordingly

 

[joerd]

SemiConGuy,

I only know the VBA answer to your question, you can define a function as follows:

Function IsFilled(r As Range) As Variant
Select Case r(1, 1).Interior.ColorIndex
Case xlColorIndexNone, xlColorIndexAutomatic
    IsFilled = False
Case Else
    IsFilled = True
End Select
End Function

If you call it like =IsFilled(A1), this will give you TRUE if A1 has a specified fill color, otherwise it returns FALSE.
Further to Kris44 's solution: a more efficient way would be to process only the so called UsedRange:

Sub macro1()
Dim Row As Long
    For Row = 1 To ActiveSheet.UsedRange.Rows.Count Step 2
        ActiveSheet.Rows(Row).EntireRow.Interior.ColorIndex = 15
    Next Row
End Sub

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.