VBA - If Cell selected and a particular colour then msg box

[hmltnangel]

Hi all,

Looking for a piece of code that does the following.

If any cell in range V4:V37 is selected, and the cell colour is orange, then a message box pops up.

Searching found me a few potential results, but the issue seems to lie in the fact that the orange is a Conditional Format rule. I can write a piece that says if cell selected then msg box, but its the only if its orange part that im stuck on.

Any suggestions?

 

[Joe4]

Place the following VBA code in the Sheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   Exit if multiple cells selected at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit if cell selected is outside of range V4:V37
    If Intersect(Target, Range("V4:V37")) Is Nothing Then Exit Sub
    
'   See if color of selected cell is orange
    If Target.Interior.ThemeColor = xlThemeColorAccent2 Then
'    If Target.Interior.Color = 39423 Then
        MsgBox "Cell is orange!"
    End If
    
End Sub

Note that you will have to identify which color orange you are using, and adjust the code accordingly.
One way to find out is to turn on the Macro Recorder, and record yourself coloring a cell the color you want to identify.
Once you have it, you can plug in into the code.
Note I commented out a line. Depending on the color you choose, it could be a "Theme Color", or just color code.

 

[hmltnangel]

So if my colour is

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0

do I need to put that theme colour in anywhere?

 

[Joe4]

The code I gave you should work then, as-is, as I already coded it for "xlThemeColorAccent2".
Did you try it yet and see if it works?

 

[hmltnangel]

I thought that Joe.

I popped this in the correct worksheet code. Then when I select any cell thats that shade, nothing happens

 

[Joe4]

Try temporarily updating the code to this, and then select a cell with that orange coloring and tell me what number the MsgBox returns:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    MsgBox Target.Interior.Color

'   Exit if multiple cells selected at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit if cell selected is outside of range V4:V37
    If Intersect(Target, Range("V4:V37")) Is Nothing Then Exit Sub
    
'   See if color of selected cell is orange
    If Target.Interior.ThemeColor = xlThemeColorAccent2 Then
'    If Target.Interior.Color = 39423 Then
        MsgBox "Cell is orange!"
    End If
    
End Sub

 

[hmltnangel]

Any cell in the range comes up with 16777215 regardless if its orange or not

 

[Joe4]

I misunderstood your original question. I thought you were saying the code you found was for Conditional Formatting. I now see that your color is being assigned by Conditional Formatting.

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    MsgBox Target.DisplayFormat.Interior.Color

'   Exit if multiple cells selected at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit if cell selected is outside of range V4:V37
    If Intersect(Target, Range("V4:V37")) Is Nothing Then Exit Sub
    
'   See if color of selected cell is orange
    If Target.DisplayFormat.Interior.Color = 15773696 Then
        MsgBox "Cell is orange!"
    End If
    
End Sub

Whatever number pops up, replace the "15773696" number in my code with that value, and then you can get rid of that temporary MsgBox at the very top.

 

[hmltnangel]

Getting closer Joe, thanks

Its a table with banded rows, it works on one shade of the banded rows, but not the other. I did try various connotations of "elseif", or "or", but cant quite get it right

 

[Joe4]

Its a table with banded rows, it works on one shade of the banded rows, but not the other.

So you are wanting to check two different colors? You did not mention this is any of your previous posts!
Please be sure to include all important details here, we are not mind readers.

What are the color numbers for the two different codes you are checking for?
Please post the code that you tried to check for the both of these.