Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Conditional formatting excel 2003 Results

This thread:

http://www.mrexcel.com/forum/showthr...itional+format

Asks almost exactly the question I have, but I can't make heads or tails out of the answers, I'm guessing becasue I have Excel 2003 and not 2007 as mentioned in the replies.

Anyway, here's my question:

In column "A" I have a number; column "B" the formula =A1 copied on down and in column "C" either a 1 or 2 or a 3.

If the number in column "C" is a 1 then the font in the same cell in Column "B" should be black, 2 Blue and 3 Red.

I'm thinking this should be very simple, but so far it's not turning out that way. )-:
.
.
.
.
.

Hi All,

I would like to use Conditional Formatting (Excel 2003 Windows) to put a
Border around two cells in the same column. The second cell to be formatted
will always be directly beneath the first cell; i.e. cell I2 (1st) and I3
(2nd).

There are two consecutive rows for each Numeric Label - the Rank is the 1st
row and the Frequency the 2nd row. The Rank is listed twice in column "B" for
each Numeric Label for sorting purposes.
Each Rank in a row is unique.

The criteria to Conditionally Format the cells - Format Rank and related
Frequency:
1. Match Rank >=10 (greater than or equal to 10) in the 1st row of the
relevant Numeric Label - CF is a Border. The Ranks to be matched may be in
any column between "E" and "I".

2. Put a Border around the cell in the 2nd row (Frequency) that corresponds
to the Numeric Label and is directly below the Matched Rank in the 1st row.

Sample Data Layout:
Columns: "A" = Numeric Label, "B" = Rank, "C" = Total, "D" = Text Labels, "E":
"I" = Rank & Frequency (6th-10th Position). The Data starts on row 2 with
Numeric Label 220. Data row 2 to 11.

Label Rank Total POS 6th 7th 8th 9th 10th
220 18 Total Rank 14 10 17 12 18
220 18 1041 Freq 7 6 5 3 3
470 16 Total Rank 6 15 13 17 11
470 16 1058 Freq 4 4 3 3 2
180 14 Total Rank 13 10 16 12 14
180 14 1042 Freq 6 5 4 3 3
400 13 Total Rank 11 14 5 23 13
400 13 1053 Freq 5 5 4 4 3
40 11 Total Rank 9 11 13 15 14
40 11 1040 Freq 6 5 4 4 3

Expected Results:
Label 220 - Row2 Column "I" Rank 18 & Row3 Frequency 3 should have an outline
Border.
Label 180 - Row6 Column "I" Rank 14 & Row7 Frequency 3 should have an outline
Border.
Label 400 - Row8 Column "I" Rank 13 & Row9 Frequency 3 should have an outline
Border.
Label 40 - Row10 Column "F" Rank 11 & Row11 Frequency 5 should have an
outline Border.

Thanks
Sam

--
Message posted via http://www.officekb.com

Excel 2003 does not allow references to other worksheets for Conditional
Formatting criteria. Does Excel 2007? Thank you, JP.

Is there a way to use conditional formatting on a text field with leading
zeros?
For example, I have a department number of 00123 and would like to highlight
all instances of this number. When entering directly in to the dialog box,
the zeros are dropped.

On the attached worksheet, I have rows of "Teams" that undergo 3 Games (starting with Game 1 and working right). Each game is divided into an "Open Time" and a "Close Time". I would like to conditionally format the worksheet in that the row is highlighted red up to where the Open Time is entered, and the row is highlighted blue up to where the Close Time is finally entered. I have manually formatted my worksheet in order to better show what I'm looking for.

In previous attempts my formatting only applied to the current cell once I entered values in the Game 2 and 3 columns.

I realize this is an Excel 2007 file, but the actual file will be managed via Excel 2003.

Hi,
Any ideas how to do the following using conditional formatting (or is it even possible?) -

I have a range of values from 0 - 100
I want to use conditional formatting to make :
values that equal 0, cells turn the colour green
values that are between 1 and 99, cells turn the colour amber
values that are greater than 100, cells turn the colour red

For examle : -
Condition 1 Formula Is = A2 = 0
Condition 2 Formula Is = IF(AND(A2>0,A2<100)
Condition 3 Formula Is = A2 > 100

Hope the above makes sense - any suggestions would be appreciated!!

Hi,

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

Rob

In short, I have a fantasy football spreadsheet I do every year for my league(s). This year, my goal was to create a conditional formatting thingy that would color the desired cells based on the colors of the team the player plays for. For example:

Tom Brady plays for the New England Patriots. His background color would be value 1, and his text color would be value 2. The team name is in cell B2, and cells A1:C2 need to be colored the same as B2. I understand that this will most likely involve a macro (which I don't understand because I haven't learned the Visual Basic language yet). Any ideas?

Thanks

I am having some difficulty with conditonal formating on a form am I am trying to create in Excel 2003.

It's pretty hard to put into words as an example so I have uploaded my spreadhseet here: http://rapidshare.com/files/149674609/Book1.xls

Basically I have a couple of lookup functions that get data from the 'Data' sheet. The look up is searching based on the data in cell B4.

This then returns the results in Cells B13:B16. Cells D13:D16 I have some conditional formatting rules.

I want certain cells to appear with data and displayed with conditonal formatting based on the selection from a drop down list.

For example:

I have conditional formating on B13 so it appears blank until a selection is made in Cell B4. Then depending on what data is chosen in B4 different information will be displayed in cells B13:B16 and different formating in D13:D16.

Conditional Formating on B13 is:

Cell Value = 0 (Format - white text with no borders)
=ISERROR(B13) (Format - white text with no borders)

Conditional formating on D16 is:

=ISERROR(B13) (Format - white text with no borders)
=NOT(ISBLANK(B13)) (Format - black text with a black border)

The Scenario:

Cell B3 contains the Text "New_User" (selected from a drop down list)
Cell B3 contains the Text "Sales" (selected from a drop down list)
Cell B4 contains the Text "BS" (selected from a drop down list)

The lookup function in B13: =LOOKUP(B4,Data!A12:B15)

This would return "Sales Rep Postal Codes:" in Cell B13. B16 should then use conditional formating to place a black border around the cell with black text.

This all works fine. However, if the selection made from the drop down list in B4 is "T" then this returns a value of "0" in B13, as a result the conditional formating on B16 is black border and black text when i want it to be no border with white text.

If the selection in B4 is "BS" or "O" then I want cell B16 to have black border and black text. If no selection has been made in B4 or if "T" is selected then I want B16 to have no border with white text.

I hope somone understands what I'm saying and can help me!! I have tried everything I can think of to get it to display teh way I want.

I have uplaoded the spreadhseet on the link above because it will probably be easier for you to understand what I'm trying to do if you look at the spreadhseet, I tried to use the HTML maker but it doesn't display correctly.

Please help! Thanks in advance!

John

I need to accomplish several tasks and I am stuck. I am limited to 3
formats so I will list them in order of Importance I know how to do
2,3,4 so if you can tell me how to do #1, I can skip #4 and get this
done.

1. If a row has anything in the first cell, format all the cells in
that row that have anything in them to a pink fill, otherwise leave
them white (no fill)
If I use .......=$A1>"" that will change the entire row 1 to the fill
color that I choose (pink), if there is data in A1. But I only want
B1, F1, and G1 to fill color pink since they have data in them. The
rest are blank. Row 2 will certainly have different cells that have
data.

2. If a cell anywhere has APL it should be red text, yellow fill

3. If a cell anywhere has Holiday in it it should be red text green
fill.

4. If a cell anywhere has HW in it, I want the fill color to be blue.

I use Excel 2003. I want to do a conditional formatting on a cell (e.g. A1), which value is from an If() formulas. The value could be a percentage or a text “DataNAvail”. The conditional formatting on A1 is based on another cell’s value (e.g. B1), which is also a percentage.

Since in Excel 2003 there are only three options for conditional formatting. I used two for green (A1 greater than B1) and yellow (formulas in A1 equals AND(A1<B1, A1>=B1*0.75)). I want to set A1 to red when its value <B1*0.75 or is “DataNAvail”. Since they are not the same type, I don’t know how to do it. Could anyone give me a help? Thank you.

Frank

Hi

I have been asked to colour code a row within Excel 2003 based on a number between 1 and 9 in the first column.

Can you have more then 4 conditions in Excel 2003 i.e.
A1 = 1, cell is red
A1 = 2, cell is green
.
.
A1 = 9, cell is 9th different colour

If this is possible, can you conditionally format a range within the row i.e.

A1 = 1, then A1:A10 are red
A1 = 2, then A1:A10 are green

Hope you can help.

Chris

Hi

I've made a spreadsheet at home on Excel 2007 which has up to 5 Conditional Formatting rules per cell which works fine. The problem is when I save as 97-2003 version to send to work, the old version of Excel only supports 3 rules as you know, is there a way of applying 4 or 5 rules when specific text is populated in a cell on the old version of Excel?

Many thanks

I have used conditional formating in a table to colour code actions for; today (red) , +1 - +7 days (green) and +7 - +21 days (blue).
How do I make the adjacent cells follow the same colour pattern, they are not in date/number format?

Hello guys!

I've been trying to use 2 FORMULAS in CONDITIONAL FORMATTING boxes.

The first condition would bold the text if value is found in a list.
The second condition would turn the cell filling to green if last 2 characters would be "_2".

But my issue is that the second condition doesn't work unless i delete the first formula.

Is there some sort of priority in conditional formatting ??

All i get is the cell being turn to bold text.

//regards
radu

Hi,

First of all I am using excel 2003 other wise this would be easier, but since you can't refrence another worksheet in 2003 when using conditional formating I was wondering If there was another way to do it.

My problem is I have two sheets, one is a plannned break down for an activity while the other is the actuall breakdown of what happend. The activity is broken down into a month by month basis, with an x percentage of hours for each month. Now what I am trying to do is say you have a total of 500 work hours, and you work 30 hours one month and the plan said it should be 20, is there a way to highlght that cell and also the remaining cells you have left to work in.

Below I attached a example work book witch will make it easier to understand

Thanks in Advance

Hello,

I am trying to do a conditional formatting formula in Excel 2003 but can't get it to work ... I am sure my math somewhere must be wrong.
I am currently using =AND(M6="Deploy", N6 90% it doesn't work (and the other 2 conditions are already full with other formulas). Any help would be appreciated.

Thank you, J

Hello All,

I am after some VBA code (Excel 2003) to conditionally format cells, dependent on a master key. I am after matching an etry in a cell to the key, then looking up that cell's formatting and applying it to the initial cell. Blank cells should be left white.

I currently have a hard-programmed conditional formatting VBA code which works, but offers no flexibility (also, although it runs in a WoksheetChange() sub, it generates lots of errors if I try to run it as a macro - I am after code for both). The data (C1:D1) will always be text.

A B C D E F G 1 I T M X J 2 Key: 3 I 4 T 5 M 6 X 7 J

So far I have something that looks like this (absolute cell references are different as the above is a simplification), but it does not work:

	VB:
	
 Range) 
     
    If Not Intersect(Target, Range("D6:EV49")) Is Nothing Then 
         
        Application.EnableEvents = False 
        Target = UCase(Target) 
        Application.EnableEvents = True 
         
        If Target.Value = Range("A62").Value Then 
            Range("A62").Select 
            Selection.Copy 
            Target.Select 
            Selection.PasteSpecial Paste:=xlPasteFormats 
            Target = UCase(Target) 
            Exit Sub 
             
            If Target.Value = Range("A63").Value Then 
                Target.Interior.ColorIndex = 36 
                Exit Sub 
                 
                If Target.Value = Range("A64").Value Then 
                    Target.Interior.ColorIndex = 44 
                    Exit Sub 
                     
                    If Target.Value = Range("A65").Value Then 
                        Target.Interior.ColorIndex = 7 
                        Exit Sub 
                         
                        If Target.Value = Range("A66").Value Then 
                            Target.Interior.ColorIndex = 4 
                            Exit Sub 
                             
                            If Target.Value = Range("A67").Value Then 
                                Target.Interior.ColorIndex = 35 
                                Exit Sub 
                                 
                                If Target.Value = Range("A68").Value Then 
                                    Target.Interior.ColorIndex = 36 
                                    Exit Sub 
                                     
                                    If Target.Value = Range("A69").Value Then 
                                        Target.Interior.ColorIndex = 44 
                                        Exit Sub 
                                         
                                        If Target.Value = Range("A70").Value Then 
                                            Target.Interior.ColorIndex = 7 
                                            Exit Sub 
                                             
                                            If Target.Value = Range("A71").Value Then 
                                                Target.Interior.ColorIndex = 7 
                                                Exit Sub 
                                                 
                                                If Target.Value = Range("A72").Value Then 
                                                    Target.Interior.ColorIndex = 7 
                                                    Exit Sub 
                                                     
                                                    If Target.Value = Range("A73").Value Then 
                                                        Target.Interior.ColorIndex = 7 
                                                        Exit Sub 
                                                         
                                                        If Target.Value = Range("A74").Value Then 
                                                            Target.Interior.ColorIndex = 7 
                                                            Exit Sub 
                                                             
                                                            If Target.Value = Range("A75").Value Then 
                                                                Target.Interior.ColorIndex = 7 
                                                                Exit Sub 
                                                                 
                                                                If Target.Value = Range("A76").Value Then 
                                                                    Target.Interior.ColorIndex = 7 
                                                                    Exit Sub 
                                                                     
                                                                    If Target.Value = Range("A77").Value Then 
                                                                        Target.Interior.ColorIndex = 7 
                                                                        Exit Sub 
                                                                         
                                                                        If Target.Value = Range("A78").Value Then 
                                                                            Target.Interior.ColorIndex = 7 
                                                                            Exit Sub 
                                                                             
                                                                            If Target.Value = Range("A79").Value Then 
                                                                                Target.Interior.ColorIndex = 7 
                                                                                Exit Sub 
                                                                                 
                                                                                If Target.Value = Range("A80").Value Then 
                                                                                    Target.Interior.ColorIndex = 7 
                                                                                    Exit Sub 
                                                                                     
                                                                                    If Target.Value = Range("A81").Value Then

                                                                                        Target.Interior.ColorIndex = 7 
                                                                                        Exit Sub 
                                                                                         
                                                                                        If Target.Value = Range("A82").Value
Then 
                                                                                            Target.Interior.ColorIndex = 7 
                                                                                            Exit Sub 
                                                                                             
                                                                                            If Target.Value =
Range("A83").Value Then 
                                                                                                Target.Interior.ColorIndex =
7 
                                                                                                Exit Sub 
                                                                                                 
                                                                                                If Target.Value =
Range("A84").Value Then 
                                                                                                   
Target.Interior.ColorIndex = 7 
                                                                                                    Exit Sub 
                                                                                                     
                                                                                                    If Target.Value =
Range("A85").Value Then 
                                                                                                       
Target.Interior.ColorIndex = 7 
                                                                                                        Exit Sub 
                                                                                                         
                                                                                                        If Target.Value =
Range("A86").Value Then 
                                                                                                           
Target.Interior.ColorIndex = 7 
                                                                                                            Exit Sub 
                                                                                                             
                                                                                                            If Target.Value =
Range("A87").Value Then 
                                                                                                               
Target.Interior.ColorIndex = 7 
                                                                                                                Exit Sub 
                                                                                                                 
                                                                                                            End If 
                                                                                                        End If 
                                                                                                    End If 
                                                                                                     
                                                                                                End If 
                                                                                            End If 
                                                                                        End If 
                                                                                         
                                                                                    End If 
                                                                                End If 
                                                                            End If 
                                                                             
                                                                        End If 
                                                                    End If 
                                                                End If 
                                                                 
                                                            End If 
                                                        End If 
                                                    End If 
                                                     
                                                End If 
                                            End If 
                                        End If 
                                         
                                    End If 
                                End If 
                            End If 
                             
                        End If 
                    End If 
                End If 
                 
            End If 
        End If 
    End If 
     
    On Error Goto 0 
     
End Sub 

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

Hi,
I have attached a worksheet that has extracted a column i have in a database i have set up. In this there are drop down menus from which the person can select from 4 options.
- On Track (Green)
- At Risk (Yellow)
- In Trouble (Red)
- Complete (Purple)
I want to be able to colour the particular cell when they select that text, for the particular color next to the option in brackets.
Because excel cannot do more than 3 conditions i need to use VBA. I have tried unsuccessfully using IF statements and select cases but i can never get them to work.
Also i have 20 sheets each with this column so is there a way to apply it to the whole workbook? Or how would i do it for each sheet?

Cheers for any help.Conditional Format Test.xlsx

I have this code on column B on my excel sheet =COUNTIF(B:B,B1)>1

but I want to create a conditional formatting where that formula will only check a series of cells, let's say I want to check if there are any duplicates 50 cells above or 50 below this specific cell (it's basically different for each cell)
is this possible? if so how can it be done?


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