Identify rows with certain background color?

  • Thread starter Thread starter DonLi
  • Start date Start date
D

DonLi

Hi,

Is there a way (programmatically) to identify rows with certain
background color say, "red"?

Thank you.

Don Li
 
Don,

You can certainly identify the cell's background color with code
like

If Range("A1").Interior.ColorIndex = 3 Then
' do something
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Don Li,

Sub test()
Dim rng As Range, rngRed As Range

For Each rng In Sheet1.UsedRange.Rows.EntireRow
If rng.Interior.Color = vbRed Then
If rngRed Is Nothing Then
Set rngRed = rng
Else
Set rngRed = Union(rngRed, rng)
End If
End If
Next

If Not rngRed Is Nothing Then rngRed.Select
End Sub


Rob
 
Chip and Rob,

Thank you both for the lighting speed response and solution.

Here's some further questions if you don't mind, also consider I've
never used ASP before but know a bit about programming. So, things like
reset "vbRed" to "vbYellow" would be easy for me but ...

Goal A is to 1) identify rows with background color yellow, so, I now
how to handle it;
2) add 'y' to the first cell in the row;
Goal B: 1) using ASP to set up a datasource connection to the
spreadsheet;
2) add/update 'y' to the first cell in the row with yellow background

If not much trouble, I'd appreciate further help. Many thanks in
advance.

Don
 
Don,

So in my code:
....
If rng.Interior.Color = vbRed Or rng.Interior.Color = vbYellow Then
rng.Cells(1, 1).Value = "y"
....

I don't have IIS running, so I can't help with ASP data sources, sorry.

Rob
 
Back
Top