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?
 
regmigrantCommented:
use autofilter to show only the red coloured cells, select them and 'format cells' to red then delete the conditional formats

Reg
0
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
James ElliottManaging DirectorCommented:
CORRECTION:

ALT+F11 (Not Shift+F11)
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.