Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting ignoring cells with a format already
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting ignoring cells with a format already
On Oct 23, 1:03*pm, Jon Dow wrote:
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 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting ignoring cells with a format already
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. -- Thanks, Shane Devenshire "Jon Dow" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to format range of cells using Conditional Formatting-Excel? | Excel Worksheet Functions | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting: format when range of cells are blank | Excel Discussion (Misc queries) | |||
Can I get rid of conditional formatting but keep format changes? | Excel Discussion (Misc queries) | |||
How do I use conditional formatting to change subtotals row format | Excel Worksheet Functions |