×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Conditional formatting cells that are NOT "Cell Highlighted"

Conditional formatting cells that are NOT "Cell Highlighted"

Conditional formatting cells that are NOT "Cell Highlighted"

(OP)
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.  sadeyes

RE: Conditional formatting cells that are NOT "Cell Highlighted"

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!

RE: Conditional formatting cells that are NOT "Cell Highlighted"

or you'll just have to do it the old way and apply the fill color on alternating rows manually sad
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.

RE: Conditional formatting cells that are NOT "Cell Highlighted"

(OP)
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

RE: Conditional formatting cells that are NOT "Cell Highlighted"

Use VBA. This has 2 advantages
  1. Allows for easy modification
  2. Reduces the size of the file by eliminating conditional formatting

CODE

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

RE: Conditional formatting cells that are NOT "Cell Highlighted"

SemiConGuy,

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

CODE

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:

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources