Identify rows with certain background color?

[DonLi]

Hi,

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

Thank you.

Don Li

 

[Chip Pearson]

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

 

[Rob van Gelder]

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

 

[Don Li]

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

 

[Rob van Gelder]

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