J
Jim C
How can I incorporate a format into a if statement?
For example
Sum if Cells fill color is red?
For example
Sum if Cells fill color is red?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
C
Corey
Try Conditional Formatting from the Format tool bar.
G
Gord Dibben
Corey
Info only.................you cannot CF on a fill color without using VBA
Gord Dibben MS Excel MVP
Info only.................you cannot CF on a fill color without using VBA
Gord Dibben MS Excel MVP
About conditional formating...
About conditional formatting...
excel has the most IDIOTIC conditional formatting EVER...!
and you know why?
Because you have to proceed this formatting one by one for every cell...!
imagine that you want to CF 100 cells... then you have to do this CF procedure 100 times...!! this is extremely time consuming and that's why other users wanted to make a CF via a worksheet function (not VBA), so they can drag the cell function and automatically CF other cells too...
About conditional formatting...
excel has the most IDIOTIC conditional formatting EVER...!
and you know why?
Because you have to proceed this formatting one by one for every cell...!
imagine that you want to CF 100 cells... then you have to do this CF procedure 100 times...!! this is extremely time consuming and that's why other users wanted to make a CF via a worksheet function (not VBA), so they can drag the cell function and automatically CF other cells too...
L
Lee
Hello there
Wondering if you can help.
I have problem with worksheet at work. Running 2003. I have in some cells
formula with if and hlookup. It picks up colour in cell pending answer. IE
if comes back due then cell says due and changes to yellow if expired then
expired returns andcell changes to red. THese have been set up for some time.
I have had to add new columns and rows. In the new ones they are changing
to due and expired but not picking up colours. I have looked into
conditional formatting but it is blocked and I cannot select it.
Can you advise something please.
Also if can help. I can't merge cells on the new ones I have added. I have
checked and the worksheet is not locked.
Thanks
Lee
Wondering if you can help.
I have problem with worksheet at work. Running 2003. I have in some cells
formula with if and hlookup. It picks up colour in cell pending answer. IE
if comes back due then cell says due and changes to yellow if expired then
expired returns andcell changes to red. THese have been set up for some time.
I have had to add new columns and rows. In the new ones they are changing
to due and expired but not picking up colours. I have looked into
conditional formatting but it is blocked and I cannot select it.
Can you advise something please.
Also if can help. I can't merge cells on the new ones I have added. I have
checked and the worksheet is not locked.
Thanks
Lee
G
Gord Dibben
If CF is not available and worksheet is not protected perhaps the workbook
has been shared under Tools>Share Workbook.
Unshare to regain CF capability.
Gord Dibben MS Excel MVP
has been shared under Tools>Share Workbook.
Unshare to regain CF capability.
Gord Dibben MS Excel MVP
E
Ed R
Is there a way to do this if you are looking for a range of values?
Say
- Turn Green if <=16
- Turn Red if cell > 16 and <= 19
- Turn Pruple of cell >19.
I can't seem to get the middle rule to work. It will run the cell Green but
I can't get the compound rule to cancel out the Green and make it red
Say
- Turn Green if <=16
- Turn Red if cell > 16 and <= 19
- Turn Pruple of cell >19.
I can't seem to get the middle rule to work. It will run the cell Green but
I can't get the compound rule to cancel out the Green and make it red
T
T. Valko
Try this:
=AND(A1>16,A1<=19)
Replace A1 with the actual cell reference
=AND(A1>16,A1<=19)
Replace A1 with the actual cell reference
B
Brittany N Stinson
Hi I would like to have cells change based on if it is the maximum of a range
of cells. For example 0 1 1 3 2 2
I would like the cell containing 3 to turn red because it is the maximum. I
have looked into the conditional formatting but dont see anything that could
help?
of cells. For example 0 1 1 3 2 2
I would like the cell containing 3 to turn red because it is the maximum. I
have looked into the conditional formatting but dont see anything that could
help?
P
paul
is there any way to write an IF statement that does the following:
=IF(A1=pink,"Yes","No")
=IF(A1=pink,"Yes","No")
T
tyronki
Hi,
I'm trying to use conditional formatting formulas to change the fill colour
of a large table of cells. The table is automatically populated with job
numbers, of which there are many. There are only 3 job types and on the same
sheet there is a separate table with a column for "job type" and a column for
"job number". I want to use a lookup function in the large table to find the
job type and then change the cell colour to suite the job type.
I've tried using formulas for conditional formating but the formulas limit
you from using certain references. i.e. I want the format of each individual
cell in a range to change according to that individual cell's own lookup
function. But all i can do is change the format of the whole range of cells
according to one cell's lookup function.
it would be so easy if you could change a cell's formatting using code in an
"if" statement.
i.e. if(vlookup(A5,$A$1:$B$25,2)="jobtype1",A5.CellFillColour=red)
I'm trying to use conditional formatting formulas to change the fill colour
of a large table of cells. The table is automatically populated with job
numbers, of which there are many. There are only 3 job types and on the same
sheet there is a separate table with a column for "job type" and a column for
"job number". I want to use a lookup function in the large table to find the
job type and then change the cell colour to suite the job type.
I've tried using formulas for conditional formating but the formulas limit
you from using certain references. i.e. I want the format of each individual
cell in a range to change according to that individual cell's own lookup
function. But all i can do is change the format of the whole range of cells
according to one cell's lookup function.
it would be so easy if you could change a cell's formatting using code in an
"if" statement.
i.e. if(vlookup(A5,$A$1:$B$25,2)="jobtype1",A5.CellFillColour=red)
B
Bernie Deitrick
You need to write a formula that returns either TRUE or FALSE, so something like this would work:
=VLOOKUP($A5,$AA$1:$AB$25,2,False)="jobtype1"
and set the fill to red...
You, of course, would need either two or three conditions - if you have only three conditions, the
default formatting can take care of one condition, and the CF can take care of the other two.
HTH,
Bernie
MS Excel MVP
=VLOOKUP($A5,$AA$1:$AB$25,2,False)="jobtype1"
and set the fill to red...
You, of course, would need either two or three conditions - if you have only three conditions, the
default formatting can take care of one condition, and the CF can take care of the other two.
HTH,
Bernie
MS Excel MVP
J
Josh
I am trying to shade the row if a particular cell in the row has a date value
in one of three categories. What I have done is not working. Statements are
below.
="If(($B$2-NOW)>=30)"
="if(($B$2-NOW)<0)"
="If(($B$2-NOW)<=30)"
It is not coloring a single cell.Any ideas of better logic to use?
in one of three categories. What I have done is not working. Statements are
below.
="If(($B$2-NOW)>=30)"
="if(($B$2-NOW)<0)"
="If(($B$2-NOW)<=30)"
It is not coloring a single cell.Any ideas of better logic to use?
S
Son of a Sea Cook
I am trying to shade the row if a particular cell in the row has a date value
in one of three categories. What I have done is not working. Statements are
below.
="If(($B$2-NOW)>=30)"
="if(($B$2-NOW)<0)"
="If(($B$2-NOW)<=30)"
It is not coloring a single cell.Any ideas of better logic to use?
You can let the internal engine do it.
K
Ken
I think the quotation marks around your IF statement are probably
messing you up.
=If(($B$2-NOW)>=30)
=if(($B$2-NOW)<0)
=If(($B$2-NOW)<=30)
You will still have the logic issues that Gord pointed out.
Ken
messing you up.
=If(($B$2-NOW)>=30)
=if(($B$2-NOW)<0)
=If(($B$2-NOW)<=30)
You will still have the logic issues that Gord pointed out.
Ken
G
Gord Dibben
Ken
Don't need to use IF
Also NOW is a function and syntax is NOW()
Gord
Don't need to use IF
Also NOW is a function and syntax is NOW()
Gord
T
tyronki
Hi, thanks a million Bernie.
My conditional formatting applies to a number of columns too- different
dates.. Your formula applies the format to the whole row. So what if I only
wanted to apply the formatting to the cell in that row which wasn't empty.
(it will contain the same job number which was used as the lookup value- i.e.
A5)
I'm running into a similar problem where the cells I want to reference in
the conditional formatting formula are the cells that the format actually
applies to.
e.g: I wish I could do this in my formula:
=AND(VLOOKUP($A5,$AA$1:$AB$25,2,False)="jobtype1", NOT(ISBLANK("cells in row
9 to which formula applies")))
my spreadsheet looks much like ms project with jobs on the left, dates along
the top and coloured cells where the dates and jobs correspond.
Cheers for the help,
Tyron
My conditional formatting applies to a number of columns too- different
dates.. Your formula applies the format to the whole row. So what if I only
wanted to apply the formatting to the cell in that row which wasn't empty.
(it will contain the same job number which was used as the lookup value- i.e.
A5)
I'm running into a similar problem where the cells I want to reference in
the conditional formatting formula are the cells that the format actually
applies to.
e.g: I wish I could do this in my formula:
=AND(VLOOKUP($A5,$AA$1:$AB$25,2,False)="jobtype1", NOT(ISBLANK("cells in row
9 to which formula applies")))
my spreadsheet looks much like ms project with jobs on the left, dates along
the top and coloured cells where the dates and jobs correspond.
Cheers for the help,
Tyron
G
Geo.
So is it now true in Excel 2007, that I have to apply conditional formatting
to each cell individually (in this case). In 2003, I was able to drag the
cell formatting down a long list of items where the formulas were relative so
the row number changed as I went from cell to cell. Excel 2007 seems to want
to lock everyting to a specific cell e.g., $m$4. If this is the case, it is
terrible. Perhaps I have missed something. I certainly hope so.
to each cell individually (in this case). In 2003, I was able to drag the
cell formatting down a long list of items where the formulas were relative so
the row number changed as I went from cell to cell. Excel 2007 seems to want
to lock everyting to a specific cell e.g., $m$4. If this is the case, it is
terrible. Perhaps I have missed something. I certainly hope so.
T
T. Valko
You can still apply the formatting to the entire range at once.
Let's assume you want to format A1:A10 if the cells contain Yes.
Select the *entire* range A1:A10 starting from cell A1. A1 will be the
active cell. The active cell is the one cell in the selected range that is
not shaded. The formula used is relative to the active cell.
Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
=A1="Yes"
Click the Format button
Select the desired style(s)
OK out
Let's assume you want to format A1:A10 if the cells contain Yes.
Select the *entire* range A1:A10 starting from cell A1. A1 will be the
active cell. The active cell is the one cell in the selected range that is
not shaded. The formula used is relative to the active cell.
Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
=A1="Yes"
Click the Format button
Select the desired style(s)
OK out
M
Matt
This method works, but I cannot get it to meet my specific needs.
By using your method, I was able to use a cell reference so that if =A1="no"
then Fill Cell RED and Apply to $B$1. This makes B1 red when A1 is "no". I
would like to apply this conditional format to an entire row B1-G1, however
it does not format all those cells, it only formats B1 even though the
conditional format shows it is applied to $B$1:$G$1. However, I CAN apply
this conditional format to colums, so that is a big help when I am dealing
with a table (make B2 red when A2="no", make B3 red when A3 . . . . and so
on). However, I can only apply these conditions to COLUMNS and for some
reason the rows won't take. Can you help me out? i keep having to apply the
same logical function to each column and telling that colum to reference
column A.
After writing this question I figured out my own answer and am now asking
another question. I realized that if my formula is =$A$1="no", then I CAN
apply the formatting easily to each row, however I cannot apply to columns
because then all rows will reference row 1 (if A1="no" then B2 is RED", but I
want them to reference in their own Row (if A2="no", B2 is RED). If I want to
apply easily to columns, my formula must be =A1="no". I realize that the
reason it is not keeping the formatting throughout rows is because when I
move over from B1 to C1, that cell is conditionally formatted based on B1,
and not A:1 which equals "no". C1 sees that B1 is not equal to "no" and
therefore doesn't turn to RED. So my new question is how do I make it so
that when I reference cell A1 in a conditional format, that the format
applies to cells in the same row (row x references cell Ax) and same column
(column x,y,&z all reference column A). In other words, I want the
referenced cell to be able to move up and down, but not side to side - how
can I get the cells around it to recognize so they can format (in rows)
according to that column? Like I said, I already have a method of doing
this, i'm just looking for a faster, easier way.
By using your method, I was able to use a cell reference so that if =A1="no"
then Fill Cell RED and Apply to $B$1. This makes B1 red when A1 is "no". I
would like to apply this conditional format to an entire row B1-G1, however
it does not format all those cells, it only formats B1 even though the
conditional format shows it is applied to $B$1:$G$1. However, I CAN apply
this conditional format to colums, so that is a big help when I am dealing
with a table (make B2 red when A2="no", make B3 red when A3 . . . . and so
on). However, I can only apply these conditions to COLUMNS and for some
reason the rows won't take. Can you help me out? i keep having to apply the
same logical function to each column and telling that colum to reference
column A.
After writing this question I figured out my own answer and am now asking
another question. I realized that if my formula is =$A$1="no", then I CAN
apply the formatting easily to each row, however I cannot apply to columns
because then all rows will reference row 1 (if A1="no" then B2 is RED", but I
want them to reference in their own Row (if A2="no", B2 is RED). If I want to
apply easily to columns, my formula must be =A1="no". I realize that the
reason it is not keeping the formatting throughout rows is because when I
move over from B1 to C1, that cell is conditionally formatted based on B1,
and not A:1 which equals "no". C1 sees that B1 is not equal to "no" and
therefore doesn't turn to RED. So my new question is how do I make it so
that when I reference cell A1 in a conditional format, that the format
applies to cells in the same row (row x references cell Ax) and same column
(column x,y,&z all reference column A). In other words, I want the
referenced cell to be able to move up and down, but not side to side - how
can I get the cells around it to recognize so they can format (in rows)
according to that column? Like I said, I already have a method of doing
this, i'm just looking for a faster, easier way.