Free Microsoft Excel 2013 Quick Reference

Conditional Formatting in Excel 2003


I have a spreadsheet that contains a list of names and a date column that is already conditionally formatted to go red when the date is six months old. It is a date to indicate when a certain form was signed. These need to be redone every six months. So, what I would like to do is set up another column with conditional formatting to turn that date back to black when the new date is input and get the new date to be ready to go red in another six months. Does anyone know if this is even 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?

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.

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


Is there any way of turning off all automatic formatting in Excel 2003?

For example Excel automatically changes strings such as 1-12 in a CSV file
to 1-Dec, 10-10-2005 to 10/10/2005 and strings of numbers to an exponential
representation. These changes are preserved when saving the file and thus
corrupting it, preventing any other applications from reading it.

I know you can import CSVs changing all the fields to text using the data
import option, however reopening it and saving causes Excel to make the same
changes again.

Any suggestions would be really appreciated as this is causing us real




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?



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 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.



I would be most grateful if anyone could assist me on the correct syntax for an IF/OR statement in Conditional Formatting in Excel 2003.

What I am trying to check for is:-

If E11 >= C11 OR if E22 <= D11 then colour the cell yellow

Any assistance would be greatly appreciated

Many thanks in advance


Hi guys
Excel 2003 only allows 3 "preset" conditional formatting.
Could anyone please tell me how to increase this.
I need approximately 8 colour variations when certain conditions are met!

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

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,

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.

Hi, I'm having trouble keeping track of conditional formatting in Excel 2003.

How can I find which cells have conditional formatting in them?

And how can I find which cells have specific conditional formatting in them?

Thanks in advance!

I have the following code in an Excel Worksheet for Conditional Formatting...

Sub setConFormat(intLL, intTar)
'intLL=0.77 and intTar=0.8
    With Selection

        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:=CStr(intTar)
        .FormatConditions(1).Font.ColorIndex = 1 
        .FormatConditions(1).Interior.ColorIndex = 43

        .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:=CStr(intLL), Formula2:=CStr(intTar)
        .FormatConditions(2).Font.ColorIndex = 1
        .FormatConditions(2).Interior.ColorIndex = 44

        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        .FormatConditions(3).Font.ColorIndex = 2
        .FormatConditions(3).Interior.ColorIndex = 1
    End With
End Sub

Here, the Selections are different parts of a Pivot Table. This code works absolutely fine in Excel 2003 but not in Excel 2007.

In Excel 2007 the FormatConditions(3).Font.ColorIndex gets applied to FormatCondtion(1)! I have no idea why this is happening! Any help would be greatly appreciated.

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 ?

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:

Is there a way to conditionally format a cell based on a date in
another cell?

In other words, cells A1:A10 contain data. C15 contains a date. If the
date is in the past, can I change the formatting in cells A1:A10? Or
any other cells for that matter?

How can I format a cell in Excel 2003 using 12-hour format but without
displaying AM/PM?
For example, for 1:00 in the afternoon (1:00 PM or 13:00), I would like to
display just " 1:00 " (without the quotation marks); not " 1:00 PM "; not "
13:00 ".
Is there a way to format the cell(s) to display time in this way?

I keep getting the Error Msg -
Too Many Different Cell Format
in Excel 2003

Anyone have any quick fixes?

Hi all

I have some code on a worksheet that basically dictates that the colour of text in column F is white unless the values of cells in column H are between 1-5 (see below).

On my Excel 2007, everything works fine and when a user selects a value between 1-5 in column H, it shows up (in black) the value of the relevant cell in column F.

However, in Excel 2003 there seems to be a delay in when this font colouring happens, and I was wondering if anyone knew why? (Unfortunately, most of the users of this worksheet will still be on Excel 2003.)

In 2003, when I select a value in H, I then need to navigate away from excel, then come back to it, and only then does the cell in column F show up as black - so is there a workaround for this? I'd be really grateful for any assistance!

Best wishes


    ' Data Validation List of numbers 1-5 and Select Score and default of ENTER SKILL LEVEL
    Application.CutCopyMode = False
    With Selection.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="<<Enter your Skill Level>>, 1, 2, 3, 4, 5"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = "Please select a Skill Level between 1-5"
        .ShowInput = True
        .ShowError = True
    End With
    With Selection.Interior
        .ColorIndex = 2
    End With
    ' Conditional Formal on column F to show values when 1-5 entered in column H
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    With Selection.FormatConditions(1).Font
        .Color = 1
    End With


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?


I've use this very useful code from Ozgrid to give me more than three cell colour conditional formats in Excel 2003, but I need to make this work on formula results.

Is there a way of making this formatting work on the results of if statements in the same cell?

Any help appreciated.

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.

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