We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Andreas Hermle
on
Medium Priority
678 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
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 ZwiekhorstSAP Business Consultant

Commented:
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 HermleTeam leader

Author

Commented:
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
SAP Business Consultant
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Saqib HusainEngineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
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 HermleTeam leader

Author

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.