Challenging Excel VBA Question 1

[wkilgroe]

I have an Excel Add-in in which I need to be able to tell if a cell background color has been modified due to conditional formatting. I thought that I could check the value of "Application.Worksheets(sheet).Cells(Row, column).Interior.ColorIndex" but the value for this always stays at the value for "No Color" rather than changing when conditional formatting has altered the color. Interestingly, if I manually change the background color, checking "Application.Worksheets(sheet).Cells(Row, column).Interior.ColorIndex" will work correctly. Is this just a flaw of Excel? Any ideas?

 

[SkipVought]

Hi,

Here's an example of what you can do...

Sub WhatColor()
    For Each fc In Selection.FormatConditions
        [A1] = fc.Formula1
        If [A1] Then MsgBox fc.Interior.ColorIndex
    Next
End Sub

where you select a cell conditionally formatted



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[Bowers74]

Since conditional formatting can only have 3 different format settings then why not just check for the values that you have set to change the cell's format?

celect case [A1]
    case is = 1
        DoThis
    case is = 2
        DoThis
    case is = 3
        DoThis
    case Else
        DoNothing
End Select

Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[xlbo]

And in case you wanted clarification, this is all because conditional formatting doesn't change the interior.colorindex of the cell - think of it as a thin coloured membrane just above the cell - making it look different but without actually changing anything - which is pretty much what formatting is all about.

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[wkilgroe]

SkipVought,

Thanks for the post. The only problem with approaching it this way is that I have about 100 columns with different conditional formatting built into them. They can leave a cell alone, make it red, or make it yellow. My add-in has to behave differently for each of these. Since the colorindex isn't actually changed by the conditional formatting, I am looking for a way to tell if conditional formatting has made each cell red, yellow, or left it alone without building all of that logic into my code(3 per column x 100 columns). Any help is appreciated.

 

[SkipVought]

Geex, wkilgroe!

That's like saying, "I'd like to get from New York to LA in 3 hours WITHOUT FLYING!"

1. you select the cell
2. you run the proceure
3. you observe the result in the message box (well, it gives you the interior color index and not the color, but some additional code could remedy that!)

How do you want it to work???

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[Bowers74]

Why do you want to test if a cell has been formatted through Conditional Formatting? I don't see the sense in this. If you explain EXACTLY what your reasoning behind this is, then maybe we might all be able to come up with a better solution to the problem.





Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[VBAjedi]

I just wrote a little code frag for you to play with:

Dim c As Range
For Each c In Selection
   If c.FormatConditions.Count > 0 Then
      MsgBox c.FormatConditions.Item(1).Interior.Color
   End If
Next c

Put that in a normal sub, select a range containing cells with conditional formatting, and run the sub.

Let me know if that helps!



VBAjedi

 

[SkipVought]

VBA

Just because a format condition exists does NOT mean that THAT cell has met the condition. If MULTIPLE format conditions exist, each must be tested. The format corresponding to the first TRUE condition is what is required. The condition needs to be evaluated, which is what my code does...

Sub WhatColor()
    For Each fc In Selection.FormatConditions
        [A1] = fc.Formula1
        If [A1] Then MsgBox fc.Interior.ColorIndex
    Next
End Sub

Since we don't know HOW he wants to implement this, one cell at a time, all cells in used ranges???

???

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[wkilgroe]

Ok,

I finally got it to work. Thank you so much for your help.