• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

Can you AND / OR coloring in Excel via VBA

Imagine that I have rows 10 - 100 where the cells either have no color or a color of blue.

I'd like to add a macro that will turn the clear cells into red (for instance).

It would be neat if the following worked:

sheetname.range(startRow, endRow).Interior.ColorIndex = red AND blue (or red NOT blue, etc)

The thought would be if the cell was blue it'd stay blue else red.

Don't want to loop through each row and determine if it is blue and then make it red if not... have too many columns to do this with.
Sheldon Livingston
Sheldon Livingston
1 Solution
Saurabh Singh TeotiaCommented:
The only other way that i can you can do this apart then loop is by applying filter..

Enclosed is the code for your reference where i assumed you want to apply filter on A Column on sheet1

Sub check()

    Dim lrow As Long
    Dim ws As Worksheet
    Dim r As Range
    Set ws = Sheets("Sheet1")

    lrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row

    ws.Range("A1:A" & lrow).AutoFilter Field:=1, Operator:=xlFilterNoFill
    Set r = ws.Range("A2:A" & lrow).SpecialCells(xlCellTypeVisible)

    r.Interior.ColorIndex = 3
    ws.Range("A1:A" & lrow).AutoFilter

End Sub

Open in new window

Sheldon LivingstonConsultantAuthor Commented:
Wow!  Perfect.  Thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now