Select Cells based on background color

[bstrahan]

This has also been posted on the MS Office forum

I am using conditional formatting to insert a background color into cells dependent on its relationship to another cell. Once this is done I want to "fix" the color so that it is no longer dependent on the conditional format, as the relationship cell changes. Is this possible either with VBA or a cell formula?

 

[SkipVought]

Hi,

Use your macro recorder to see the code behind the CF range AND removing the CF.

Use the appropriate criteria value to assign the Interior.Colorindex property.

Post back with your code if you need help customizing. Will also need to have a description of the range under consideration.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[cresbydotcom]

Would looking at properties in the watch window reveal anything? eg Range("C27").FormatConditions.Item(1)

watch for (say) cells(a,b)
single step then expand the "+" and look down the hierarchy for likely candidates.

Range("C27").FormatConditions.Item(1).Interior.Color
give the colour when true
Range("C27").FormatConditions.Item(1).Formula1
gives the value for true but I cant see the test (my case "equals")
but if you write your own formula you might spot it in the list of properties. then you would have to test for the condition - unless they are linked values in another spreadsheet not much saving.

Best of luck

there is a tide in the affairs of man that you Cnut ignore.................

 

[SkipVought]

faq707-4594


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}