How does a macro check for cell (fill) colour?

[50gumbys]

Hi everyone!

I have a situation with a macro (slippery li'l sucker!)

I want this macro to check a cell for a condition.
If the condition is met, it should branch into a particular path.

Now the problem is this - the condition to check is the cell colour (fill colour, not conditional formatting - or are they same????).
How do I check for that ????

I'm sorry - I just don't know how to do this! :dunno

And I could SO use your help!!
Thank you so much !!!!!
(yup - I DO talk in upper caps!)

[johnske]

Hi 50gumbys,

If it's any use, this will return the colour of the selected cell (but as a colour 'number') :bink:


Sub Colour()
MsgBox ("This cells colour is " & ActiveCell.interior.Color)
End Sub

[johnske]

PS this is the underlying fill colour, not any colour being shown because of conditional formatting conditions having been met :bink:

[Jacob Hilderbrand]

You may want to use the ColorIndex as well.


MsgBox ("This cells color is " & ActiveCell.Interior.ColorIndex)

[Richie(UK)]

Hi,

As John pointed out, the Interior.ColorIndex code applies to 'normal' colour fills. It does not apply to colours applied by Conditional Formatting.

To help establish the index that you are looking for try the following in a new workbook - it will colour the cells in column A and show the index in column B.


Sub ShowColours()
Dim lCnt As Long
For lCnt = 1 To 56
Sheet1.Cells(lCnt, 1).Interior.ColorIndex = lCnt
Sheet1.Cells(lCnt, 2).Value = lCnt
Next lCnt
End Sub

You can then use this information to construct your test - like this:


Sub Test()
Dim rngToTest As Range
Set rngToTest = Sheet1.Range("A5")
Select Case rngToTest.Interior.ColorIndex
Case 1: MsgBox "It's black"
Case 5: MsgBox "It's blue"
Case Else: MsgBox "It's not black or blue!"
End Select
End Sub

HTH

[johnske]

I think the lady is looking for something along these lines


Sub CompareByCellColour()
If ActiveCell.interior.Color = ActiveSheet.Range("A1").interior.Color Then
'//do something (EG)
MsgBox ("It's the same colour")
Else
'//do something else (EG)
MsgBox ("It's NOT the same colour")
End If
End Sub
In which case it doesn't really matter if she uses Color or ColorIndex. But then again maybe she's looking at several cases and needs a test... :bink:

[50gumbys]

Thank you so much for your input!

I must apologise for the confusion I've initiated here - but I've just got more 'dope' on the situation and, in fact, it DOES relate to conditional formatting.

My apologies for the incorrect information!

[johnske]

..........................- but I've just got more 'dope' on the situation and, in fact, it DOES relate to conditional formatting........Hi 50gumbies,

In that case you really only need to know the condition(s) that initiate the format e.g. "If a cell value is greater than 10 then colour the cell yellow"

If you still need assistance with this, please post an example of some of the conditions and what you need to be done if the conditions are met and someone will give you the required code :)

Regards,
John :bink:

EDIT: Alternatively, "zip" a copy of the book and post it here with info on wot needs to be done (just remove any confidential info and make a few "dummy" entries in it)

[Zack Barresse]

This may be of use to you Lori ...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=190