Excel 2010 - Conditional formatting

Dear Experts,

Maybe my question is very simple, I have a range =$CC$93:$CC$110 where the conditional formatting rule color everything to red if the cell value is less than =CO93.

My target to keep this coloring result as the fiscal week is finished for that column, but basically what is the method for that? If I remove the conditional formatting then the color disappear, if I change any value in CO93 that is again reflecting on the target cells.

Briefly how keep the color AND remove the conditional formatting?

thanks,
LVL 1
csehzIT consultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James ElliottManaging DirectorCommented:
You'll either need some code to do it, or make the formatting conditional on two cells. Firstly, CO93, and the other a cell containing the fiscal week or something. That way, it will only change if the week is within defined parameters.

A copy of your workbook would be useful.

Jell
0
James ElliottManaging DirectorCommented:
This would also work.

Hit Shift+F11.
Double click relevant sheet on the right.
Paste this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

For each cel in [CC93:CC110]
   If cel.Interior.Color = 16777215 then
        cel.FormatConditions.Delete
   End If
Next cel

End Sub

Open in new window

0
James ElliottManaging DirectorCommented:
CORRECTION:

ALT+F11 (Not Shift+F11)
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

csehzIT consultantAuthor Commented:
Jell thanks the answer, I have deleted some columns so the structure is in the file with weeks and the formatting.


File-0301.xlsx
0
csehzIT consultantAuthor Commented:
Jell sorry maybe I am doing something wrong, tried the attached code on the example file but that one colors everything to black in column L, not keeping the colors


Sub test()

For Each cel In [L4:L21]
   If cel.Interior.Color = 16777215 Then
        cel.FormatConditions.Delete
   End If
Next cel

End Sub

Open in new window

0
regmigrantCommented:
use autofilter to show only the red coloured cells, select them and 'format cells' to red then delete the conditional formats

Reg
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James ElliottManaging DirectorCommented:
Sorry, I forgot that conditional formatting doesn't actually register as a color when tested programmatically.

Remove all conditional formatting and try this code behind the relevant sheet. Change the ranges to suit your purpose.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

For Each cel In [CC93:CC110]
   If Not cel.Interior.Color = vbRed Then
        If cel > [CO93] Then cel.Interior.Color = vbRed
   End If
Next cel

End Sub

Open in new window

0
csehzIT consultantAuthor Commented:
Jell and Reg, thanks for both approaches, with these methods it works finally however good to know that it is not so simple
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.