Conditional Formatting ignoring cells with a format already

[Jon Dow]

I have a spreadsheet that contains some cells that are manually colored (not
by conditional formatting). However, I want to run a conditional format on
the whole table but only have it apply to cells that are not colored already.
Can this be done? Here is a quick example:

Name Jan Feb Mar
Smith 150 215 195
Johnson 0 145 110
Fisher 125 95 115
Sweeney 165 125 210
Wright 110 130 140
Dunn 25 150 120
James 65 130 105

The cells for Johnson for Jan & Feb and the cell for James Mar are already
highlighted by me (in yellow) for other purposes. I want to do say Red at 75
or less, green at 76-125, and orange 126+. However, I do not want to change
my yellow cells only the cells that do not have a background color now. Can
this be done with conditional formatting? Thanks

 

[Spiky]

I have a spreadsheet that contains some cells that are manually colored (not
by conditional formatting). However, I want to run a conditional format on
the whole table but only have it apply to cells that are not colored already.
Can this be done? Here is a quick example:

Name    Jan     Feb     Mar
Smith   150     215     195
Johnson 0       145     110
Fisher  125     95      115
Sweeney 165     125     210
Wright  110     130     140
Dunn    25      150     120
James   65      130     105

The cells for Johnson for Jan & Feb and the cell for James Mar are already
highlighted by me (in yellow) for other purposes. I want to do say Red at75
or less, green at 76-125, and orange 126+. However, I do not want to change
my yellow cells only the cells that do not have a background color now. Can
this be done with conditional formatting? Thanks

I don't think so. I believe you can only reference the format via a
UDF. And it doesn't seem like you can use this UDF in conditional
formatting. At least, it won't work for me, it thinks it is an
external reference. Conditional formats have to stay within the same
worksheet.

Can you duplicate the yellow-highlight reason in conditional
formatting? As a 2nd criteria.

Or, another thought. If you are going to the trouble of manually
highlighting, can you go to the trouble of manually NOT selecting
those cells when you do your conditional format?

 

[ShaneDevenshire]

Hi,

You can't do it with conditonal formatting unless you use code. Here is the
VBA needed:

Sub MyFormat()
Dim cell As Range
For Each cell In Selection
With cell
If .Interior.ColorIndex <> 6 Then
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue,
Operator:=xlLessEqual, Formula1:="75"
.FormatConditions(1).Interior.ColorIndex = 3
.FormatConditions.Add Type:=xlCellValue,
Operator:=xlBetween, Formula1:="75", Formula2:="125"
.FormatConditions(2).Interior.ColorIndex = 4
.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, Formula1:="125"
.FormatConditions(3).Interior.ColorIndex = 44
End If
End With
Next cell
End Sub

To run this macro select the range and run it. This macro assumes you
formatted the cells with the bright yellow in 2003 or earlier.

If this helps, please click the Yes button.