asked on

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,

James Elliott

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

James Elliott

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

James Elliott

CORRECTION:

ALT+F11 (Not Shift+F11)

csehz

ASKER

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

File-0301.xlsx

csehz

ASKER

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

ASKER CERTIFIED SOLUTION

regmigrant

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

James Elliott

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csehz

ASKER

Jell and Reg, thanks for both approaches, with these methods it works finally however good to know that it is not so simple