Free Microsoft Excel 2013 Quick Reference

Conditional Formatting in Excel 97/2000

I'd like to format cells in RED if the value is below 0, GREEN if above 0 and WHITE if equal to 0. I can get this to work in Excel 2003 fine, by using a separate rule for each, but when the XLS is opened in 97 or 2000, it doesn't work.

Is this possible ?

Post your answer or comment

comments powered by Disqus
Is it possible to have more than 3 conditional formats in excel? I find that
I need 4 formats.
I am using Excel 2000. Can Excel XP or 2003 use more than 3 formats?

Is there any way to get more (up to 10 at least) specifications for
conditional formatting in Excel?


I am familiar with setting up to 3 conditional formats in Excel spreadsheets
but I would like to be able to set up to 10, say.
Does anyone know how I can do this?

Many thanks

I am new to conditional formating in Excel.

In row 2 I need to enter nos. between 1-5. I want each color to have a
particular color. I have managed to do 4 (3 with conditional formating and
the 4th retaining the default color).

Is there a way i can do all 5 colors?


Had several different types of conditional formatting in a sheet that had
been designed using Excel 2003 (*.xls). Converted the sheet to (*.xlsx)
format. I can successfully add and edit new conditional formatting to the
sheet, and when you save it, it appears to save all changes, but the
conditional format changes are not saved. Any ideas about what is going on


Ostate in Houston


I am facing an issue here with conditional formatting of excel. My cell is changing color on basis of value of another cell.
For e.g. if cell D3='R' then color of cell A1 should change to red
I am using formula in conditional formatting as =D$3="R" then set color as red. I have a macro which insert a row and column. I want that this conditional formatting to get copied too. It is copied successfully and new formula becomes =D$4="R" while i want it to become =E$4="R" as both row and column are inserted. Can anyone help me in this?


Hello all -

I'm trying to successfully create a 3rd Conditional Format in my workbook with VBA. I know that with two conditions one gets the FirstPriority and the second gets the LastPriority, syntactically. What I can't seem to find anywhere is how to arrange the code to account for a Third condition.

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ 
Selection.FormatConditions(1).StopIfTrue = True 
 ' Second Condition
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ 
Formula1:="=0.01", Formula2:="=93.99" 
With Selection.FormatConditions(2).Font 
    .Bold = True 
    .Italic = False 
    .Color = -16777024 
    .TintAndShade = 0 
End With 
With Selection.FormatConditions(2).Interior 
    .PatternColorIndex = xlAutomatic 
    .ThemeColor = xlThemeColorAccent6 
    .TintAndShade = 0.799981688894314 
End With 
Selection.FormatConditions(2).StopIfTrue = False 
 ' Third Condition
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ 
With Selection.FormatConditions(3).Font 
    .Bold = True 
    .Italic = False 
    .Color = -16776961 
    .TintAndShade = 0 
End With 
With Selection.FormatConditions(3).Interior 
    .PatternColorIndex = xlAutomatic 
    .ThemeColor = xlThemeColorAccent3 
    .TintAndShade = 0.399945066682943 
End With 
Selection.FormatConditions(3).StopIfTrue = False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The above order is how I'd like to set up the Conditional Formats. But the code is only highlighting blank cells with an Orange Fill. I want the Second Condition to highlight all cells with values between 0.1% and 93.99% with an Orange fill and bold Red Fond.

For the Second condition I've tried .SetSecondPriority and .SetNextPriority but that line fails when I've tried both of those scenarios. What is the syntax for a Second condition?

Thanks, guys.

Ok, I just read about conditional formating in a thread from 2003 on
another site that I found on google.... It was quite helpful but I was
wondering if there was a way to go further.... no one was answering me
on the other site so I thought I'd try another.....

I have a thing I need to do in the first cell, then as I complete 4
steps to the thing I add a "1" in the next 4 cells one at a time. Every
time on step gets completed though, all 5 cells change color so you can
tell at a glance how far you are by its color.

Well, I wanted to know if there was a way to say "if A2 is equal to 1
but A3 isnt make A1:A5 Red" and so on so I don't have to manually have
to change the color each time.

Just curious.... I'm learning a lot about Office lately.....


BaptistKitty's Profile:
View this thread:

I built a worksheet with conditional formating in excel 2010, however, end users are using 2003, and you can not use the amount of conditional formating in earlier versions.

Select Home -> Conditional Formatting (in Styles Group) -> Highlight Cells Rules or Top/Bottom Rules.

Select Home -> Conditional Formatting (in Styles Group) -> New Rule.

Select Home -> Conditional Formatting (in Styles Group) ->Clear Rules -> Selected Cells or Entire Sheet.

Select Home -> Conditional Formatting (in Styles Group) -> Manage Rules.

I am using Excel 2000, and right now I have 3 conditional formats...but need one more......can I have 4 conditional formats??


I am running Excel 2003 and I wish to apply conditional formatting to only cells with format Number. Does anyone knows an easy way of doing this or a VBA code for it?

I have tried on my own to write a code , but as I am new to VBA , I can't figure out how to make it work . The code supposed to compare all the numeric values from an uknown number of columns with numeric values from column B row by row and Bold the ones greater. The number of columns and rows is uknown because the data will be exported from another software to Excel , so bassically I wish to format a woorkbook to automatically do that.
The code is below:

    Dim Count As Integer 
    Dim Count1 As Integer 
    Dim nr1 As Range 
    Dim nr2 As Range 
    Dim nr3 As Range 
    Dim nr4 As Range 
    Dim nr5 As Range 
    Dim nr6 As Range 
    Dim nr7 As Range 
    Dim nr8 As Range 
    Set nr1 = Range("A65536").End(xlUp) 
    Set nr2 = Range("a1").End(xlToRight) 
    Count = Range("A1", nr2).Columns.Count - 1 
    Set nr3 = Range("A1", nr1.Offset(0, Count)) 
    nr3.Name = "Data" 
    Set nr4 = Range("B65536").End(xlUp) 
    Set nr5 = Range("B7", nr4) 
    Set nr6 = Range("D7").End(xlToRight) 
    Set nr7 = Range("D65536").End(xlUp) 
    Count = Range("D7", nr6).Columns.Count - 1 
    Set nr8 = Range("D7", nr7.Offset(0, Count)) 
    nr8.Name = "Values" 
    For Each r In nr8.Rows 
        i = r.Row 
        If IsNumeric(Cells(r)) = True And r.xlCellValue > nr5.xlCellValue = True Then 
            cell.Font.Bold = True 
        End If 
    Next r 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thank you very much for any help received.

I want to apply conditional formatting to a cell (i.e. shade it in grey) based on the value in another cell. For example, i have a form and depending to the type of request the user has selected, i want to grey out the fields they do not need to fill in.

Is that possible in Excel 2007?

Hello all. I am attempting to conditionally format cells in a column of data. I want to shade any cell that is not the same value as the cell immediately above it. For instance, I want the conditional formatting to shade ONLY THE FIRST cell that contains the 460.03 value in the example below. I can't quite figure out how to make this happen. I tried to use Conditional Formatting>Highlight Cells Rules>More Rules>Use a formula to determine which cells to format, and then I just plugged in a < with reference to the cell immediately above, but I couldn't get that to work. I also tried to upload my Excel file, but couldn't get that to work either (probably due to my own ineptitude). Thought I would try without uploading anyway, for now.
541.08 541.08 541.08 460.03 460.03 460.03
All of these cells contain formulas, if it makes any difference.


I am having difficulty in copying a conditional formatting formula in excel 2007.
Basically, I want the cell that I conditionally format (A1) to have a colored fill if a reference cell (A4) has any number in it.
I can get that to work in one cell, but when I copy it to another cell (B1), it references the same cell as the originally formatted cell (A4), not the new cell I want to refer to (B4).

A1 A2 A3 A4

B1 B2 B3 B4

I would appreciate any help you can give.

I have a sales spreadsheet that I want to add a splash of colour too.

Currently I have some colour but run out when I used 3 conditional formats.

Could this be done in VBA. and if so please could you help with the script.

I have written an IF statement in cell P2:P... as a helper coloumn that works out the status of each order

The status are as follows

"1" = No format
"2" = Background Colour light blue (A:I)
"3" = BGC = Peach = (A:I)
"4" = BGC = Yellow = (A:K)
"5" = BGC = Blue = (A:L)
"6" = BGC = Orange = (A:I)

so if P6 = 4 then cels A4:K4 need to be coloured yellow.

I hope this is clear.

Thanks for your help

I have used conditional formatting to color 5 cells in a row if 2 of them are >0. I now want to copy this to the remaining rows of the range (about 50 of them).

Can anyone help me please

Thank you - I am using excel 2007

David Williams

Just got Excel 2007 and am looking at sheets created in Excel 2002. I can't seem to find a way of viewing the conditional formatting that I created in 2002. Any help will be appreciated.

Hi all!

I have the following code working wonderfully in Excell 2000, but it gives me an error (something about range) when trying to duplicate the file in Excel 97.

Can anyone tell me where it needs to be adjusted to make it work in E97? I have in blue the line that gets tagged in the debugger.

Sub myFindX()
'Find all the rows that have your organization in it on sheet2.
'copy part of that row to the next blank row on Sheet1.
Dim Message, Title, Default%, MyValue%, myTest

On Error GoTo myErr
'Get organization number from user.
Message = "Enter an Organization Number:" ' Set prompt.
Title = "Find Employees!" ' Set title.
Default = 0 ' Set default.
' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)

Application.ScreenUpdating = False
For Each r In Worksheets("Active_Employee_Data").UsedRange.Rows
n = r.Row
If Worksheets("Active_Employee_Data").Cells(n, 1) = MyValue Then
myTest = True
x = x + 1
'Copy that Row's cells "A through I [or 1 through 9]" of the row with the Found class.
'to the other sheet [Sheet 1] in the next empty rows of column "A!"
Worksheets("Active_Employee_Data").Range(Cells(n, 1), Cells(n, 9)).Copy _
Destination:=Worksheets("Risk_Assessment_Form").Range("A65536").End(xlUp).Offset(1, 0)

End If
Next r

Application.CutCopyMode = True
Application.ScreenUpdating = True
GoTo myEnd

'Wrong organization data!
MsgBox "You must enter a valid organization number!"

'Missing organization.
If myTest = False Then
MsgBox "Organization Not Found!"
End If

Columns("B:B").Select Columns("B:B").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
End Sub


Thank you!!

I like the way that Excel 2007 handles conditional formatting for
overlapping ranges and such, but sometimes I have to save files
in .xls format. Invariably I get a warning that the conditional
formatting will not render correctly in older versions of Excel.

Is there a version of "Paste Values" for conditional formats that
applies the formatting directly to the cells? It would not longer keep
up with changes, but for presentation purposes it would work just

For example, I have a table of about 2200 rows by 50 columns. Going
down a particular column, most of the cells are zero, and the nonzero
values tend to occur in clumps. Using number formatting 0.0_);(0.0);;@
I blanked out the zero values. Using conditional formatting, I was
able to highlight the first cell of a cluster one color, the middle
cells a second color, and the last cell (before going back to zero) a
third color. I also have some conditional formatting that marks
certain rows, and lots of other stuff going on to help with data
visualization. In all there are 8 conditional formats, and it looks
just the way I want it to look.

I'd like to be able to save this as an Excel 2003 file and keep the
formatting. I know that Excel 2003 can't handle all these conditions,
but I would like a way of just applying the formatting-as-displayed to
the cells before saving. Something analogous to how "Paste Values"
will give you the value-as-displayed without the underlying formula.


Hi, I work with invoices from my suppliers to make the payments and I put in
excel sheet with invoice number and supplier like

A2: supplier 1 - inv. number
A3: supplier 1 - inv. number
A4: supplier 2 - inv. number
A5: supplier 3 - inv. number

What I'm trying to made is shade the rows from supplier 1 as white, supplier
2 as gray 25%, supplier 3 as white or any other color. I have many suppliers
and vary from month to month. The conditionnal format doesn't find a way to
solution. I have tried to put a condition IF(A2A1) gray else white, but in
the next row the A3=A2 it will paint as white instead gray. My objective is
distinguish the suppliers at a glance. Thanks
With best regards,

In Excel 2007, when copying formats of one cell to another, conditoinal
formatting rules are added to the destination cell instead of overwriting the
conditional formatting of the destination cell.

How do I format the all line using data in one cell?
I new to do it in Excel 2003 but in 2007 I can do only "fancy" formating, but can't find the good old conditional formating.

I use conditional formatting in excel extensively but cannot figure out how
to set it to indicate cells that are blank.

No luck finding an answer? You could always try Google.