asked on

Search for shaded cells with a RGB fill (191, 191, 191)

Dear Experts:

on the active sheet I would like run a macro that ...
... searches for cells with a cell fill of RGB (191, 191, 191)

The number of cells in the active sheet with such specific properties is to be displayed in a Message Box: from 0 to n occurrences.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas Hermle

Saqib Husain

Try this macro. This will check the used range for the color.

If you need to check beyond then replace usedrange with the range you are looking in. If you need to check the entire sheet then you can delete the "UsedRange." part from the second line.

Saqib

Sub findclr()
For Each cel In ActiveSheet.UsedRange.Cells
If cel.Interior.Color = RGB(192, 192, 192) Then
MsgBox ("cell " & cel.Address & vbCrLf & "is rgb(191,191,191)")
End If
Next cel
End Sub

Open in new window

Eric Zwiekhorst

dear Andrea

It should be something like this..

Sub searchGrey()
Dim rang As Variant
Dim colore, cntr As Variant
rang = ActiveSheet.UsedRange
cntr = 0
For Each cell In ActiveSheet.UsedRange.Cells
colore = cell.Interior.ColorIndex
If colore = 15 Then cntr = cntr + 1
Next

If cntr > 0 Then
MsgBox "I did find " & cntr & " occurence(s) of grey in the worksheet"
Else
MsgBox "I could not find a grey orrucence in the worksheet"
End If

End Sub

Andreas Hermle

ASKER

Dear both:

thank you very much for your great and professional support. Both codes have got a different approach and both work just fine.

Before awarding points I got a question for saqib:
The cell adress of the found occurrences is displayed one by one.

Is it possible to list all the found cell addresses in one MsgBox at the end of the macro.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

ASKER CERTIFIED SOLUTION

Eric Zwiekhorst

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saqib Husain

If there are too many cells you could have something like this. You can modify

n mod 5

to increase or decrease the number of columns displayed

Sub findclr()
For Each cel In ActiveSheet.UsedRange.Cells
If cel.Interior.Color = RGB(192, 192, 192) Then
cellist = cellist & IIf(n Mod 5 = 0, vbCrLf, "     ") & cel.Address
n = n + 1
End If
Next cel
MsgBox ("the following " & n & " cells have color rgb(191,191,191)" & cellist)
End Sub

Open in new window

Andreas Hermle

ASKER

Dear both,

great job of both of you. I suggest splitting the points since both code works.

Thank you very much for your great and professionale support. I really appreciate it.

Regards, Andreas