Free Microsoft Excel 2013 Quick Reference

Conditional formatting questions.

Is there a way for me to set the conditional formatting in excel so that the formatting would be affected by the contents of another cell?

For example, if I were to enter ‘red’ in cell A1, cell B1 would have red for its cell shading.

Just to ask, is there a way to add more than 3 conditional formatting conditions to any particular cell?


Post your answer or comment

comments powered by Disqus
Hello all. I have a couple advanced conditional formatting questions that I hope someone on this forum can answer. I've done extensive searches on this forum and in books to no avail so I apologize if these have already been asked. I'm fairly a beginner to Excel so please consider in your reply.

OK, here are my questions:

1. I have a reference cell that I want all other cells to format off of. The reference cell (B3) has a formula (=INT((TODAY()-B2)/365.25)) for calulating age. Cell B6 which is blank needs a conditional format so if the age is between 10 - 18 it formats it one color and puts a check icon in the field. If it's before that range it changes the color and puts an exclamation point in and if it's greater than 18 it puts an X in.

2. On another row, if the age (reference cell) is equal to either 13, 16, 18 or 21 then I want to put a check in the box and color code the background.

If anyone can help me out with this I would be greatly appreciated as I'm totally stumped. Thanks!


Hello, need abit of help with conditional formatting. Been trying to get this right for ages but havent been able to.

Basically ive made a spreadsheet for money. It has money in, money out and a total column. The total column is calculated by the "cell above in the total column" + the money in of the next row minus the money out. I then dragged that forumla all the way down so it does it for a decent length but the only problem is that the total is showing up all the way down the page. I want to try and hide the total so it only shows up in the latest row where figures have been entered.

Please ask questions if you dont understand what I mean and any help would b appreciated. Thanks

Hello again.

I have search, but couldn't find the answer to my question. I have a feeling like it is pretty simple, but I just can seem to figure out the syntax.

I have two columns, and I want to conditionally format the second colum based on the first.

Column A Column B
Complete 1 (Format #1, because Column a = "Completed")
Complete 4 (Format #1, because Column a = "Completed")
Pending 4 (Format #2, because Column a = "Pending")
Not Started 3 (Format #3, because Column a = "Not Started")

What am I missing in conditional formating to make this work?

Sorry gang, same old question again, I have searched, but the searches haven't explained anything to me really, and I don't just want to copy and paste something for the sake of it, I actually would like to learn something from it too!

OK, I'm stuck with the same old problem, want conditional formatting but have more than 3 conditions, so I need some code.

I am tracking how many different type trades people I have on a project.
Those positions are:

Fitter = Yellow
Boilermaker = Blue
Rigger = Purple
TA = Buff (Light yellow)
Team Leader = Burgundy

Now what I need to know is the code for this, but I also wnat to know where the colours are in the code.
is there a colour chart?
If I don't like the colours, I need to know how to change the code to get the colours I'm after.
Also if I have code already in a spread sheet, how do I add new code, with out destroying the new code.

I realise this is kinda newbie questions, but it may just help me to learn.

Thanks in advance guys!

Hopefully you guys will be able to help with these; I think the first should be fairly simple. How do I incorporate wild cards in to conditional formatting? Usually you can use a * as part of and expression to denote that it could be several different items is possible to use it in conditional formatting. The other question is I have been reading about trying to sort by conditional formatting. I realize there is no real way to do it, and have read the post about using functions to find return the color of text or the background. I have tried to use those functions to sort with but they do not return the color of the conditional formatting, is there something else I can try. I am trying to avoid putting all of the formulas in cells and then sort by that.

Hi all,
I have a sequence of five columns, they look something like this:

Criteria X Y Z SUM
a 0 1 3 4
b 1 0 5 6
c 7 9 0 16
d 0 0 2 2
e 0 7 0 7
f 4 0 0 4

Is there a way to use conditional formatting to mark the entry in the
criteria column for these conditions:
Nonzero number only in first column
Nonzero number only in second column
Nonzero number only in third column
Nonzero number only in first and second column
Nonzero number only in first and third column
Nonzero number only in second and third column

I know this is kind of a complex question, thanks in advance!

PS, thanks so much to the person who helped me with my last question if he
reads this, I was sick for a day and I couldn't get back here, then I
couldn't find my old post to thank you!


I have a spreadsheet that gets updated monthly and looks like the example below

Jan Feb march April May June
21,000 23,500 22,224 22111 20,111 25,000

I've asked this question before and have gotten great tips but each month
when refesh the pivot table it looses all the conditional formatting and I
start over again.

The conditional formatting needed is if #'s go down for three consecutive
months in a row they are highlighted in red. I need the first month of
dropped revenue example above would be March in yellow and then the second
month they've dropped would be orange (March) and then the third +
consecutive months (May) would be highlighted in red. The other problem with
information received before is that when I apply the conditional formatting
as condition 1 and then 2 and then 3, even months that have not been
consecutively down for three months are highlighted and I have to go back and
take formatting out one by one. The formatting is to only take place for 3
or more consecutive drops in a row. Any assistance with this and also tell
me how to prevent the formatting from being cleared every month when I
refresh the pivot table will be greatly appreciate. There are now over 4,000
rows in the database so the time it takes is tremendous.

Using excel 2007,

Is it possible to copy conditional formatting with multi criteria ?
If so I have not been able to figure it out, tried format painter, copy/paste special, etc...nothing has worked for me !!

Example: =$G6="John Doe" $A$6:$H$6 will turn green
=$G6="Frank Smith" $A$6:$H$6 will turn yellow
=$G6="Bill Jones" $A$6:$H$6 will turn pink

I know that the formulas contain Absolute Reference, in CF but I have not been able to get around that, excel will not allow me to delete.

What I would like to do is have this Conditional Formatting applied to multiple rows in the worksheet, A6:H166

I have tried everything I know, plus researched/read quit a bit and still no resolution.

Perhaps VBA is my only option ? , but if that is the case I am SOL considering I am not a code writer.

Any help will be much appreciated.

Thank you in advance !!


I have a Conditional Formatting question that is driving me crazy.
I don’t know if it is possible in Excel 2000 but I have a simple IF formula in a cell that gives "" on certain conditions.The problem I have is that in another cell I apply conditional formatting – green >100%, red

Hi gang

say for example

A1 has a value of 40 and all cells from a2 to a10 have numbers total LESS than A1
would the conditional format to make them red be



i have two columns of numbers.

if the number in B1 >= the number in A1 - i want B1 to turn green
if the number in B1 = A1-1 - i want B1 to turn yellow
if the number in B1 = A1-2 - i want B1 to turn red

if there is no number in B1, i do not want conditional formatting to apply.

any suggestions? i'm not familiar with using VBA code.



Can conditional formatting be used to distinguish between a formula and a hard-coded value in a cell?

I know how to apply conditional formatting for things such as equal to, greater than or equal to, less than, etc., but can I apply conditional formatting to have a blank cell fill with color when a number and/or text is entered into that cell?

Hi, I have a conditional formatting question that I need help with. I have all of the hours of the day displayed in my spreadsheet (i.e. 0900,1000,1100,etc.) in different cells. Is there a way to use conditional formatting to have the hour of the cell "pop red" for its hour?... So, example 0900 would be red for the entire hour of 0900 and then at 1000, 0900 is not red anymore but 1000 is...



I have entered a value in cell Q46.

I want Q46 to have a background value of 6 if:

It is equal to zero
Cell N46 has a value of "S".

Question 1:
How do I do that with conditional formatting?

Question 2:
How do I apply the conditional format to Q46:Q1500?


I want to add some conditional formating to cells which contain dates (UK style for example 14/12/07).

If the date has passed, I want the row or a specific cell to turn red.

I tried "If greater than date ()", but this does not work.

Any help would be greatly appriciated.

Kind regards,



This one might take some explaining, but I'll try my best for you!

In a new sheet, I have re-created the UK Lotto playing slip. By this, I mean I have created and accurately lined-up small rectangles which, when filled, cover the numbers on the slip, for all 7 boards (A-G).

In another sheet, I have rows of 6 numbers, which I would like to play as lines in the Lottery.

I want to be able to adapt the sheet/s (using CONDITIONAL FORMATTING) so that the numbers I have chosen, fill-in the corresponding rectangles on the play-slip template.

I can type in the numbers 1-49 on each section of the slip (in white text, so that they are not seen).

I am thinking I will need a formula that says:

If the first row of numbers chosen reads; 1,8,21,30,34,41 - then I want the corresponding numbers on board A of the playing slip to be filled (in black).

Anyone have any ideas how I could do this?

PS: Providing this gets solved, then I am willing to forward the excel sheet to anyone who would like a copy - would be perfect for anyone who plays many lines in the draws. Took a good while to line up all the cells, to the pixel!

Thanks guys

I sure could use some help. I am trying to conditional formatting in Excel or some other function to color a matrix based upon some business rules. I am trying to build out a matrix that will show the time frame we need to issue a purchase order for an item (yellow) and when we need to verbally commit to ordering an item (blue).

What I am trying to do for each line item in the spreadsheet shown is to:
1 . Color the Current Week (at bottom) yellow on the “Calendar Week Volume” matrix.
2 . Color the “Calendar Week Volume” matrix yellow if the value is between Current Week and Current Week plus Ours-1.
3 . Color the other “Calendar Week Volume” matrix blue if they are between the # 2(above) and Current Week plus Total.

Any thoughts?



Is there a way to set a row of cells to change color when I enter any text into the cells? If so, how do I phrase that in the conditional format window?

Hey guys,

I hope you can help me out with this

I have a spreadsheet that looks like the following:


Start Date
End Date



In Progress

So now, the first question is a conditional formating question, (i looked
this up on google, but i found this mod (row??? command, could not get it to
work though) so column B is composed of drop down boxes with those 3 options.
Now, i would like it so when the closed option is chosen the entire row turns
grey aka, a certain format.

Secondly, It seems that we have a lot of projects that are similar to projects A
and B above. The duration period has ended yet they still show as open. How do I
program excel so it automatically changes the status of these projects to closed
and subsequently turns that row gray or whatever format?

Thank you for looking and helping me out!

I have had trouble posting this, forgive the disappearance and reappearance of it.

This is complicated. I need help with a conditional formatting statement. Here is the situation. I have a number in B45. It changes based on a reading taken.

B42 gets one of three statements in it. VML, CLK_Hold or CLK_Setup. Now in B24 I have a number that B45 has to be greater than if B42 = VML. In D24 I have a number that B45 has to be greater than if B42 = CLK_Hold. And I have in F24 I have a number that B45 has to be greater than if B42 = CLK_Setup.

One more problem. The user will change the numbers in B24, D24 and F24 based on other conditions before executing the macro that creates the number in B45.

If B45 is less than the corresponding number and condition, the cell has to appear red with bold white letters. So I need help writing the conditional format equation to do the following:

If B42 = "VML" then if B45 is less than B24 and B45 does not equal "", then B45 (shows up red with white letters), else If B42 = "CLK_Hold" then if B45 is less than D24 and B45 does not equal "", then B45 (shows up red with white letters), If B42 = "CLK_Setup" then if B45 is less than F24 and B45 does not equal "", then B45 (shows up red with white letters).

Any takers? I am going to keep trying, but intervention by any of you would be GREATLY appreciated.



I have a report setup where I paste data into a range of cells.
The cells have a conditional formula to compare the cells to a list of cells
in column A.

I use this report on a monthly basis, and the list in column A changes every
So the easiest way for me to start the new month is to make a copy of the
completed report for the previous month, and simply delete the data that I
had pasted in.

Only problem is when I delete that data, it also deletes the conditional
format for those cells

Is there a way to delete delete the data in a cell, but keep the conditional


I'm trying to compare a cell to another cell. If the cell that I'm
setting up the conditional formating is greater than or equal to the
other cell then I want the font to change color.

I used "Cell Value is" "great than or equal to" "=j4" j4 is the other
cell and selected red for the font color but it does not change color.

magoobee's Profile:
View this thread:

Is there a way to have Excel look in a particular cell, and if the value in
the cell meets a requirement (such as Complete), then shade the entire row a
different color? I can use conditional formatting to get the one cell
shaded, but I can't get the rest of the row to be shaded.



I have a very big spreadsheet that is sorted by customer name. I have it
subtotaled by using Data>Subtotals. I want to bold each of the subtotal
values. In the example below, that would be the number, "6.00".

ABC Co. 2.00
ABC Co. 4.00
Subtotal ABC Co. 6.00

I tried using Conditional Formatting to say bold every number with the
formula, "=SUBTOTAL(9,D910)" b/c the #s that Excel subtotals, like the
"6.00" in the ex: above, have a formula in the cell with "=SUBTOTAL(...)".
It's not working.
The spreadsheet is too big to go through manually and bold the subtotals so
I need some kind of formula to put in the 'Formula is:' box of the
Conditional Formatting dialog box to do this for me.

Can someone help?

Thank you very much,

P.S. Thank you, Max for the help on my other conditional formatting question

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