[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-04-29
7
Medium Priority
?
583 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
0
Comment
Question by:AndreasHermle
  • 3
  • 2
  • 2
7 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35489621
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

0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35489622
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
0
 

Author Comment

by:AndreasHermle
ID: 35489881
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Accepted Solution

by:
Eric Zwiekhorst earned 1000 total points
ID: 35489943
Sub searchGrey()
Dim rang As Variant
Dim colore, txt As Variant
Dim i As Integer
rang = ActiveSheet.UsedRange
i = 0
For Each Cell In ActiveSheet.UsedRange.Cells
colore = Cell.Interior.ColorIndex
If colore = 15 Then

txt = txt & " : " & Cell.Address
i = i + 1
End If
Next

If i > 0 Then

MsgBox "I did find " & i & " occurence(s) of grey in the worksheet. Cells are " & txt
Else
MsgBox "I could not find a grey orrucence in the worksheet"
End If

End Sub

0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 1000 total points
ID: 35490075
Here is mine
Sub findclr()
For Each cel In ActiveSheet.UsedRange.Cells
If cel.Interior.Color = RGB(192, 192, 192) Then
cellist = cellist & 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

0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35490106
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

0
 

Author Closing Comment

by:AndreasHermle
ID: 35490740
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question