Free Microsoft Excel 2013 Quick Reference

Conditional Format multiple cells

Hi, I's trying to conditionally format a range of cells A1:A5 by changing the background to green when cell A5 has the text "Approved" any suggestions?

Sorry this is a bit basic but I have been struggling with this for an hour and probably can't see the wood for the trees

Thanks for your help


Post your answer or comment

comments powered by Disqus
I have a spreadsheet in Excel 2007 I use to project "future values", based on weekly (historical) inputs. Each week I add the last week's numbers, and it updates the projected "future values" column. So the "future values" column changes a little every week.

I also have a set of "milestone" values. For example, the set (500, 750, 1038, 1247, 2563, etc.) offers some "milestone" values for the sake of discussion - I might have fewer or more milestones in a week.

I would like to conditionally format my "future values" column to highlight the single cells that are "closest to/greater than" each of my "milestones".

The conditional format formula should compare the "future values" column against each value in a set of "milestones", and highlight each single cell closest/higher than each of the "milestones". It should answer the question "which week will I meet or break the '500' milestone? Ahh...week 12. The '750' milestone? I see...week 23. The 'xyz' milestone?..." The milestones don't each need to be formatted differently...they could all be blue or whatever, just so they "pop".

I have used the following conditional format formula, however it does not meet all the requirements:

=$B3=((VLOOKUP(750,$B$3:$B$211,1,TRUE)))

where "B" is the future values column, "750" is a milestone, and "TRUE" selects nearest value rather than exact value.

1) it doesn't compare against multiple values...the one value is hard-coded into the formula, so can't be changed on the fly, and
2) it picks the closest/smaller value...not the closest/larger value.

Can someone suggest some syntax that meets my criteria?

If the milestone '750' in the formula could just be substituted with a range like 'Z1:Z10', that would be great...not that easy, apparently.

Hi,
In conditional formatting is there a way to say:
if cell value is "A" or "B"
then apply format...

I have two potential cell values that will be entered and would like the format to be set in either condition. I can't seem to come up with an "OR" technique for that.

Thanks!

Hi, everybody.

Within Conditional Formatting I am using the formula
=$A34=TEXT(TODAY(),"mmmm")
to automatically highlight a cell displaying the current month from the 1st of the month.

I want to have a number of cells, i.e. A34 down to say, A50 to be highlighted so I have a block of cells allotted to the current month.

I have tried various formulae without success.

Can anyone help me with this?

TIA for any advice.

I am formatting text cells based on dates in column E. However, I want the first condition ( which will be no formatting changes if the referenced cell has any date entered) to be based on the presence of any data entered into column F. I am sure the formula for the first condition is simple but I am experiencing brain lock. Help if you can. Thanks in advance.

I have been trying to Conditional Format a cell that has 6 different color options for formating. The formulas I have used for VBA will work as long as I type the data into the cell I want to change color on. The problem I am having is that the cell I want to format the color of is the result of a formula. How do I get the conditional formatting to react to the calculated cell? Any help would be greatly appreciated.

Thanks..

I need to conditionally format a cell if it is less than or equal to the value in the cell below it . However, if there is an "NA" in the cell below it, then the cell is formatted - as if the "NA" is a 0 (and thinking the formatted cell is less than the cell below it.) How do I set up the conditional format to format the cell if the cell below it is less than or equal, but "NA" (or any other type of text)?

Thanks.

HI guys

It's been a while. Is it possible to conditional format a cell which is offset from the one you are currently entering? i.e if A1 is being entered, depending on what value is in it, B1 then turns green? If it is possible is it then possible to colour a range of cells in the same fashion please?

Hi,

does anyone how I can use conditional formatting for cell swhich do have dependents and or precedents?

Thx in advance

Hi!
I noticed that in Excel 2007 and 2010, if you select a range with numbers and go to Conditional Formatting > Highlight Cell Rules and choose any numerical comparison operator (Greater Than, Less Than, Equal To), in the RefEdit control Excel would suggest you some default value. Any ideas where does this value come from?
Apparently, it is a function of the range, but I positively can't figure out what is this function. It is not an average, as some articles in the Net would suggest. BTW, googling did not help either...
I have attached the Excel file with some arbitrary figures. You can check what I am trying to explain.

Last note - I run my Excel 2007 in a compatibility mode, if this helps.
Any ideas or suggestions are welcome. Thanks in advance.

Hey everyone,

Is there a way to format multiple cells (color, border, text) by selecting a format from a drop-down list? Perhaps by copying the multiple cells from one worksheet and placing them in another?

I'm attempting to use a =IF statement for the time being, however I am not aware of a function that copies, or formats color or border.

Ex. If I have a drop down list (A1) that contains:

1) blue with border
2) green with text "green"
3) red, no border

I want to be able to select any of the 3, for ex. "blue with border" and in cell B1 change the cell to a blue fill with a border.

A macro might work more efficiently with multiple cells from a single selection of the drop down menu, but I'm not sure.

Thanks
-Hans

I am wondering how to conditional format multiple cells(highlight with a color) based on one cell.

example:

if a1 is "yes", then a1:a9 is highlighted yellow
if a1 is "no", then a1:a9 is highlighted blue

is this a IF/THEN formula using color codes?

How can I conditionally format a cell based on the value in a different cell.

I have two columns of information. None of the information in the cells I am
trying to format is numeric. I am trying to track names to loans that are
with the company. If they wrote outside, I need to keep track of which other
company it was written with.

The First column currently has a conditional format to highlight the cell if
the value is = "Home". The other values that are entered are variations of
"Outside". I have to keep the variations as they are, in order to track which
loans went with which other company.

The second column has the borrowers name. I need this column to highlight
the cells if the loan was written outside.

So if the cell is highlighted in column A, it should not be highlighted in
the column B.
And Vice versa.

Is it possible to have more than 3 conditional formats per cell. I am
creating a chart which I would like the cells to change color dependant on
the letter inserted.

I have a Excel 2003 spreadsheed with conditional formatting: a cell changes
from red, to green, to yellow, depending on the value entered into the cell
-- it works fine. When the spreadsheet uploads into SharePoint 3.0 (published
with spreedsheet functionality), the colored cells all change to a light red
color. What must I do to keep the color assignments in the Excel spreadsheet
so they appear the same when viewed in SharePoint?

Is this even possible? I've done conditional formatting to a cell based on
that same cell's info BUT what I want to do now is something like this...Can
I apply conditional formatting to cell A3 based on the information in cell
B3? If so, how do I do that?? I completely suck at writing formulas so, if
you know the answer, can you help me with how to write the formula, too?
Basically, I want to apply formatting to A3 if B3 has a value greater than
zero. Thank you!!

Hey all,
I am trying to have conditional formatting of cells apply only if another cell is blank in 2007.

A3 has a formatting of =MOD(ROW(),2)=1, then =AND(A3<>"",A3+15<=$G$1) to change the color based on date and finally =AND(A3<>"",A3+30<=$G$1) to again change the color again by date.

What I would like to do is for the cells in columb A to change only if corresponding columb B cell is empty, without using macros if possible.

How do I set the conditional formating for cell D2 based on the value in cell
G2?

Does anyone know a formula to conditionally format a cell to change color when a date is entered into another cell?

Specifically, I have a date in one cell that turns red when it is 7 days past. I want that cell to then turn to green when I enter a completion date in another cell.

Anyone?

how does one do this?

i know how to conditional format, but not with formulas.

I went to Conditional Formatting - Highlight Cell Rules - More Rules - Use a formula

but didn't know where to go from there. For formula I entered =if(A2=A1) and left the formula at that, and then underneath pressed the format button to make the preview which color I wanted. But the formula itself is erroneous.

Can anyone help a novice? I'm trying to conditional format a cell based a result in any of 5 other cells.

I want Cell A1 to remain normal if the number in that cell does not match any of the numbers in cells F1:F5 (or if there aren't any numbers in cell A1 or F1:F5). But if the number in Cell A1 matches any of the numbers in cells F1:F5 (and there won't be duplicates) I want Cell A1 to change to a red background.

I've tried some of the basic formulas that I know without much luck. I was hoping there would be someone out there where this wouldn't take much effort and they were willing to help.

Any comments are appreciated.

Conditional formatting: Highlight cells in a column that contain 1 or more of a set of words.

Below: I want all cells in colum B that contain the words "CAT", "MONKEY", or "BULL" highlighted regardless of other punctuation or words in the cell.

I found the following formula but it does not work:
=IF(ISERROR(IF(LEN(B1)>0, FIND(","&LOWER(B1)&",", ",cat,monkey,bull,"),#VALUE!)),"=",B1)

Please see attached spreadsheet.

A B
1 CAT TIGER
2 MAN
3 CHICKEN, DONKEY854
4 DUCK" SNAKE
5 MAN
6 SNAKE, MONKEY 2658
7 MONKEY: TIGER
8 MAN(
9 DOG, BIRD 4564
10 BIRD, SNAKE
11 TIGER, DONKEY
12 MAN
13 LION, MONKEY
14 BULL, LION
15 DONKEY, TIGER
16 MAN
17 COW, SNAKE..4
18 PIG, COW
19 MAN
20 OWL, MONKEY

Notice some words are not separated by spaces or are followed by or preceeded by punctuation (if the word is in complete I want the cell to be highlighted regardless of what is before or after).

I am using Excel 2003, and was told the above formula should be entered in the cell vale/equal to....

Thank you,
Daniel

Hello

Does anybody know how to set the conditional formating to cells with formulas?

I have x2 cells (let's call them A & B) with long, complicated IF AND formulas and values in them are different depending on what i type in other cells linked to them

I have set the conditional formatting 'if not between cells a & b, highlight in red' , but it doesnt work at all. I think it doesnt see the cells as values and freaks out seing the formula

HELP!

All:

I am attempting to use either a user-defined function in VBA or a
spreadsheet function to conditonally shade a cell.

I would use Excel's conditional formatting; however, it is limited to
three conditions.

Also, I can do this with VBA. Unfortunately, then I would have to
re-refernce the subroutine when the data is moved. Given the dynamics
of this spreadsheet, I would be required to do this often.

Is there anyway to use something like this,IF(A1=1, ColorIndex=1, "") ,
to conditionally format a cell?

Thanks in advance.

Floyd

conditional formatting works very well with numbers. Is there a way to
conditionally format a cell that contains words? For example if the cell
contains "confirmed" would be green and "decline" would be red - works. What
about if the cell conatins "confirmed" but also something else in that cell?
Thanks


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