
Conditional Format: If A Cell Equals One Of Four Numbers


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Conditional Format: If A Cell Equals One Of Four Numbers  Excel

View Answers


Hello,
I already have 2 cond formats going on the same column but i need 4 more for 4 different numbers. Is there a way i can make ONE formula for ALL of them.
I want any cells that include 7,14,21 and 28 to go coloured.
Is this a possibility?
Cheers
Similar Excel Video Tutorials
Conditional Format Based On Diff Cell
 See how to create conditional formatting based on a different cell. See how to highlight (add formatting to a cell) a number based on a date in a diff ...
Conditional Format w DATEDIF function
 See how to use Conditional Formatting and the DATEDIF function to add formatting to a cell based on the content in a different cell. See how to create ...
Format Negative Numbers
 See how to use Conditional Formatting to add red to negative Numbers and Green to Positive Numbers, but only use one True/False formula! ...
Helpful Excel Macros
Format Cells as Time in Excel
 This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that inst
Format Cells as Text in Excel
 This free Excel macro formats a selection of cells as Text in Excel. This macro applies the Text number format to cells
Similar Topics
Hello All
Is there a way to copy a conditional format down as I would copy a formula? For instance I am working on inventory numbers and want to input conditional formats based on whether the actuals are higher or lower than plan. Thus if higher the cell would be RED and if lower, the cell would be GREEN.
I have formulas in columns O and P. I want the conditional format in Column P to read that if this number is higher then the formula result in column O make it red, if lower or lesser make it green. However how do I copy the cond format down like I would use paste special selecting formulas. I want the cell value to change with each row making the cond formats go for instance cell P6  P26 looking to cells O6O26 respectively. Hope I make sense. I tried the paint formatter and that does not work since it continues to pull the original formula cell being O6.. thanks!
Hi  I have a series of cells which have number in them. I add conditional
formats so that if the value in the cell is above certain thresholds the
cell is coloured red/amber/green (traffic light repoprts). The thing is when
the report is printed the numbers still show.
Anyone got any ideas as to how I can "blot" out the numbers so they don't
show??
I have tried colouring the numbers white/transparrent, adding conditional
formats the the font (or something similar)!!
Cheers
Andrew
Hello,
I am trying to use a cond. format that will highlight a cell if it is eg. 5,10,15,20,25,30,35,40,45,50 etc
My setup is like this:
A1
25
B1
Revert to macro
So because A1 is a multiple of 5, that cell and B1 needs to be highlighted. (all the numbers have a description next to them that i would like highlighted aswell)
I highlight both cells and then i do a conditional format of 'Formula Is' and then '=IF=$A1=5;10;15;20;25' but it yields no results.
Can anybody modify my formula to make it work?
Thank you
Hi
In a column of numbers (which is currently using the 3 maximum conditional formats for mac excel 08), I use a helper column (i.e. B column) next to the column of numbers to place a highlight next to the largest value in the column of numbers:
(FORMULA IS)
=A1=LARGE(A$1:A$10,1)
However once I erase the column of numbers and the cells are blank, the conditional format triggers down the whole B column (im guessing because blank cell = 0 therefore all the blank cells are considered to be the largest value). Is there a way of making the condional format trigger only when there are values in A column?? Ive tried inserting A1>0 in the formula but no luck.
Im sure its something simple but i cant figure it out.
I am settin up conditional formatting on a cell as follows:
A1  contains value to be comapred
B1  cell to be coloured based on A1
At present:
Cond 1  formula is =B1>94 [then green  works fine)
Cond 2  formula is =???????? [needs to check is A1 is >80 and
I did a search on the board for this, but I gotta say, It flat out confused me...I didnt find a post that was similar to what im trying to do...and the ones i did find I could have sorta used, but I had no idea how to reedit it hahah....could someone please give me a hand here?
I already have 3 conditional formats being used but need 2 more.
my ranges are from 3 to 3822 in all my columns..
Basically I need the following:
the 4th conditional format: If any cell in column "B" equals ERI......then ...look in column Y in its adjacent cell, and if the number in the cell in Y is >30 then highlight the row of cells (not the font) from A to AB in green.
the 5th conditional format: Same as above but look for FUGEM in column B (instead of ERI)
Maybe it could all be done in the 4th conditional format??..Saying look in column B for ERI or FUGEM and then look for the >30 and make green??
so for example say B445 has ERI.........and Y445 has a value of 52......then A thru AB would highlight green.
(also...when i attempted to go into VB, i did not see any code writen down....shouldnt something be there if i already have 3 conditioanl formats on the worksheet already???
Hello. I'm using Excel 2007.
I have a file with multiple worksheets where there are several conditional formats.
On sheet one I have several that look like this but with different text conditions/colors:
Cell Value Is equal to ="Sales"
[if the cell value is Sales, puts Sales in yellow highlight]
On Worksheet 2, I also want to apply several conditional formats similar to the one above but with different texts. For some reason, I have been able to apply now four cond'l formats successfully, but I can't seem to get any more to work.
All my cond'l formats on sheet one work, but can't seem to continue the process on sheet two past the four I have already entered.
The values in the cells on both worksheets are all calculated based on a vlookup formula that links to another worksheet.
Please advise!
Hi
Yesterday I asked about the possibility of conditional formatting a range of
20 random numbers into 2 different formats. Thank you for that, but now it
seems that 3 of my 20 numbers are now requiring a different cell to be
highlighted in yet another colour.
Currently the situation is as follows:
Column G has a list of random numbers ranging from 120, with columns either
side pertaining to the random number located in the relevant cell in Column
G.
It now appears that 3 numbers in this random list do not require
highlighting in Column G and have amended the condition formatting for Column
G accordingly, but do require the data located in Column I, relevant to the
random number to be highlighted in a 3rd colour.
Is there any way I may be able to get the cells in Column I to highlight in
a colour subject to the random numbers in Column G (being 7, 13 & 16)?
Hope this makes some sense.
Many thanks.
Ellie
Hi there everyone.
I have a worksheet with many columns of information for a record that serve several purposes. I currently copy some of the columns I need and Paste Special...Values into a new worksheet and then some different columns into another new sheet. Each workbook generated will need this process carried out several times. Note: the extracted data is used as a "load sheet" for uploading in to our SAP Master Data, so the columns and format (text not formula) is quite important. This is of course becoming arduous and prime for some code.
Here is what I think I would like (built on my still reasonably limited VBA ability).
I would like to have a macro that invokes a userform (or similar) to ask (radio button?) which column set i would like:
1. SAP FLOC Upload columns
2. Instrument label detail columns
3. Other individually selected columns. If the 3rd option is selected, then another userform that lists all the column labels which can be then selected (tickbox style?)
The macro would then copy the desired columns and Paste Special...Values and Column Widths into a new worksheet. This worksheet would be placed at the End (behind all the existing visible worksheets) and the user would be prompted to give the worksheet a name.
I have used looked at some old/existing code as well as record a new macro for the (easy) part of Copy>PasteSpecial...Values into a new sheet, but don't think I can pull off the whole deal myself...hence asking for some help
I have generated the following example book to show what the raw data and new worksheet would look like.
Raw Data:
Sheet1
A
B
C
D
E
F
G
H
I
J
2
Column Label 1
Column Label 2
Col Label 3
Col Label 4
Col Label 5
Col Label 6
Col Label 7
Col Label 8
Col Label 9
Col Label 10
3
COL 1, ROW 3 text  formula derived
COL 2, Row 3 text  with Cond format
C3, R 3 text
C4, R 3 text
C5, R 3 text
C6, R 3 text
C7, R 3 text
C8, R 3 text
C9, R 3 text
C10, R 3 text
4
COL 1, ROW 4 text  formula derived
COL 2, Row 4 text  with Cond format
C3, R 4 text
C4, R 4 text
C5, R 4 text
C6, R 4 text
C7, R 4 text
C8, R 4 text
C9, R 4 text
C10, R 4 text
5
COL 1, ROW 5 text  formula derived
COL 2, Row 5 text  with Cond format
C3, R 5 text
C4, R 5 text
C5, R 5 text
C6, R 5 text
C7, R 5 text
C8, R 5 text
C9, R 5 text
C10, R 5 text
6
COL 1, ROW 6 text  formula derived
COL 2, Row 6 text  with Cond format
C3, R 6 text
C4, R 6 text
C5, R 6 text
C6, R 6 text
C7, R 6 text
C8, R 6 text
C9, R 6 text
C10, R 6 text
7
COL 1, ROW 7 text  formula derived
COL 2, Row 7 text  with Cond format
C3, R 7 text
C4, R 7 text
C5, R 7 text
C6, R 7 text
C7, R 7 text
C8, R 7 text
C9, R 7 text
C10, R 7 text
8
COL 1, ROW 8 text  formula derived
COL 2, Row 8 text  with Cond format
C3, R 8 text
C4, R 8 text
C5, R 8 text
C6, R 8 text
C7, R 8 text
C8, R 8 text
C9, R 8 text
C10, R 8 text
9
COL 1, ROW 9 text  formula derived
COL 2, Row 9 text  with Cond format
C3, R 9 text
C4, R 9 text
C5, R 9 text
C6, R 9 text
C7, R 9 text
C8, R 9 text
C9, R 9 text
C10, R 9 text
10
COL 1, ROW 10 text  formula derived
COL 2, Row 10 text  with Cond format
C3, R 10 text
C4, R 10 text
C5, R 10 text
C6, R 10 text
C7, R 10 text
C8, R 10 text
C9, R 10 text
C10, R 10 text
11
COL 1, ROW 11 text  formula derived
COL 2, Row 11 text  with Cond format
C3, R 11 text
C4, R 11 text
C5, R 11 text
C6, R 11 text
C7, R 11 text
C8, R 11 text
C9, R 11 text
C10, R 11 text
12
COL 1, ROW 12 text  formula derived
COL 2, Row 12 text  with Cond format
C3, R 12 text
C4, R 12 text
C5, R 12 text
C6, R 12 text
C7, R 12 text
C8, R 12 text
C9, R 12 text
C10, R 12 text
13
COL 1, ROW 13 text  formula derived
COL 2, Row 13 text  with Cond format
C3, R 13 text
C4, R 13 text
C5, R 13 text
C6, R 13 text
C7, R 13 text
C8, R 13 text
C9, R 13 text
C10, R 13 text
14
COL 1, ROW 14 text  formula derived
COL 2, Row 14 text  with Cond format
C3, R 14 text
C4, R 14 text
C5, R 14 text
C6, R 14 text
C7, R 14 text
C8, R 14 text
C9, R 14 text
C10, R 14 text
15
COL 1, ROW 15 text  formula derived
COL 2, Row 15 text  with Cond format
C3, R 15 text
C4, R 15 text
C5, R 15 text
C6, R 15 text
C7, R 15 text
C8, R 15 text
C9, R 15 text
C10, R 15 text
16
COL 1, ROW 16 text  formula derived
COL 2, Row 16 text  with Cond format
C3, R 16 text
C4, R 16 text
C5, R 16 text
C6, R 16 text
C7, R 16 text
C8, R 16 text
C9, R 16 text
C10, R 16 text
17
COL 1, ROW 17 text  formula derived
COL 2, Row 17 text  with Cond format
C3, R 17 text
C4, R 17 text
C5, R 17 text
C6, R 17 text
C7, R 17 text
C8, R 17 text
C9, R 17 text
C10, R 17 text
18
COL 1, ROW 18 text  formula derived
COL 2, Row 18 text  with Cond format
C3, R 18 text
C4, R 18 text
C5, R 18 text
C6, R 18 text
C7, R 18 text
C8, R 18 text
C9, R 18 text
C10, R 18 text
19
COL 1, ROW 19 text  formula derived
COL 2, Row 19 text  with Cond format
C3, R 19 text
C4, R 19 text
C5, R 19 text
C6, R 19 text
C7, R 19 text
C8, R 19 text
C9, R 19 text
C10, R 19 text
20
COL 1, ROW 20 text  formula derived
COL 2, Row 20 text  with Cond format
C3, R 20 text
C4, R 20 text
C5, R 20 text
C6, R 20 text
C7, R 20 text
C8, R 20 text
C9, R 20 text
C10, R 20 text
21
COL 1, ROW 21 text  formula derived
COL 2, Row 21 text  with Cond format
C3, R 21 text
C4, R 21 text
C5, R 21 text
C6, R 21 text
C7, R 21 text
C8, R 21 text
C9, R 21 text
C10, R 21 text
22
COL 1, ROW 22 text  formula derived
COL 2, Row 22 text  with Cond format
C3, R 22 text
C4, R 22 text
C5, R 22 text
C6, R 22 text
C7, R 22 text
C8, R 22 text
C9, R 22 text
C10, R 22 text
Spreadsheet Formulas
Cell
Formula
A3
="COL "&COLUMN()&", ROW "&ROW()&" text  formula derived"
Conditional formatting
Cell
Nr.: / Condition
Format
B3
1. / Formula is =IF(LEFT(B3,3)="COL",TRUE,FALSE)
Abc
New worksheet:
Sheet2
A
B
C
D
E
2
Column Label 1
Column Label 2
Col Label 4
Col Label 6
Col Label 8
3
COL 1, ROW 3 text  formula derived
COL 2, Row 3 text  with Cond format
C4, R 3 text
C6, R 3 text
C8, R 3 text
4
COL 1, ROW 4 text  formula derived
COL 2, Row 4 text  with Cond format
C4, R 4 text
C6, R 4 text
C8, R 4 text
5
COL 1, ROW 5 text  formula derived
COL 2, Row 5 text  with Cond format
C4, R 5 text
C6, R 5 text
C8, R 5 text
6
COL 1, ROW 6 text  formula derived
COL 2, Row 6 text  with Cond format
C4, R 6 text
C6, R 6 text
C8, R 6 text
7
COL 1, ROW 7 text  formula derived
COL 2, Row 7 text  with Cond format
C4, R 7 text
C6, R 7 text
C8, R 7 text
8
COL 1, ROW 8 text  formula derived
COL 2, Row 8 text  with Cond format
C4, R 8 text
C6, R 8 text
C8, R 8 text
9
COL 1, ROW 9 text  formula derived
COL 2, Row 9 text  with Cond format
C4, R 9 text
C6, R 9 text
C8, R 9 text
10
COL 1, ROW 10 text  formula derived
COL 2, Row 10 text  with Cond format
C4, R 10 text
C6, R 10 text
C8, R 10 text
11
COL 1, ROW 11 text  formula derived
COL 2, Row 11 text  with Cond format
C4, R 11 text
C6, R 11 text
C8, R 11 text
12
COL 1, ROW 12 text  formula derived
COL 2, Row 12 text  with Cond format
C4, R 12 text
C6, R 12 text
C8, R 12 text
13
COL 1, ROW 13 text  formula derived
COL 2, Row 13 text  with Cond format
C4, R 13 text
C6, R 13 text
C8, R 13 text
14
COL 1, ROW 14 text  formula derived
COL 2, Row 14 text  with Cond format
C4, R 14 text
C6, R 14 text
C8, R 14 text
15
COL 1, ROW 15 text  formula derived
COL 2, Row 15 text  with Cond format
C4, R 15 text
C6, R 15 text
C8, R 15 text
16
COL 1, ROW 16 text  formula derived
COL 2, Row 16 text  with Cond format
C4, R 16 text
C6, R 16 text
C8, R 16 text
17
COL 1, ROW 17 text  formula derived
COL 2, Row 17 text  with Cond format
C4, R 17 text
C6, R 17 text
C8, R 17 text
18
COL 1, ROW 18 text  formula derived
COL 2, Row 18 text  with Cond format
C4, R 18 text
C6, R 18 text
C8, R 18 text
19
COL 1, ROW 19 text  formula derived
COL 2, Row 19 text  with Cond format
C4, R 19 text
C6, R 19 text
C8, R 19 text
20
COL 1, ROW 20 text  formula derived
COL 2, Row 20 text  with Cond format
C4, R 20 text
C6, R 20 text
C8, R 20 text
21
COL 1, ROW 21 text  formula derived
COL 2, Row 21 text  with Cond format
C4, R 21 text
C6, R 21 text
C8, R 21 text
22
COL 1, ROW 22 text  formula derived
COL 2, Row 22 text  with Cond format
C4, R 22 text
C6, R 22 text
C8, R 22 text
Excel tables to the web >> http://www.exceljeaniehtml.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
I am going to try and bodgy something up over the weekend if I get time but would appreciate any help whatsoever in achieving this or even with suggestions on how to do it better.
Thanks very much for taking the time to read this far and in advance for any help you provide.
Regards,
Darren
Hi,
On Sheet1 I have column A that has a list of numbers, some of which have the cell coloured yellow.
On Sheet2 I have a range C3:Z150 in which the numbers from Sheet1 also appear.
How can I automatically have the cell of the numbers on Sheet2 coloured yellow, if the cell (number) is yellow on Sheet1?
Does that make sense?
Ak
I am very new to excel so please bear with me.
I have set up some conditional formatting so that a cell changes fill due to the text entry ie
'High' = red
'Med' = yellow and
'low' = blue
I then set up another conditional format so if the cell in an adjacent column contained 'closed' the cell that previously had been coloured according to priority would turn white......all well and good until I tried with excel 2003 which only enables you to have 3 conditional formats per cell.
What I intend to do to get around this is, if possible, for the cells in the first column only to be coloured when they are assigned high,med or low if the cell in the 2nd column says 'open'
I hope I have expleined this clearly enough and would be extremely grateful for any help.
hi, I have a scheduling workbook used to track activities day to day with conditional formats relating to each validation option or activity...
I would like to be able to include more than the 3 color limit and I have attached an example in which I would like each option to be a different colour and fill the corresponding cells...I've also included the colors I need relating to each option...
Is there a VBA method (maybe a for loop?) I could use for this as this will also help me with another issue I have when copying and pasting over the conditional formats
I have read the FAQ re this issue (http://www.ozgrid.com/VBA/excelcond...tinglimit.htm) but am still a bit stuck as I need it to apply the conidtional formats to a range rather than just 1 cell
Any help would be greatly appreciated
Hi
I have a conditional format that uses the following formula:
=IF(C1VLOOKUP($B1,NamedRange,1,FALSE),1,0)
This is applied on range C:C
'NamedRange' is Column C on another sheet within the same workbook.
This conditional format is used to highlight entries on one sheet that don't match the other (taking into account that the two sheets are not necessarily sorted in the same order, but column B contains the unique reference numbers)
Is there any way I can include this formula in some sort of CountIf so that I can also present a total showing how many cells have been matched (or not matched as the case may be)?
Hi,
I ran into a strange problem. I've used fill to colour a certain range of cell, but when I enter text or numbers in cells adjacent to the coloured cells (or even in cells not directly adjacent but a column apart from the coloured cells) those cells also change colour (aquiring the same colour as the filled cells). I haven't a clue what is causing this, but I did run into the same problem before when using conditional formatting (months apart and in different files altogether), so it seem as though not only the fill is mysteriously copied but the entire cell format.
Has anyone ever run into something similar? And did anyone ever find out how to solve this?
I'd be very grateful if someone helps me solve this annoying problem!
Cheers,
Philoe
I have a spreadsheet with various conditional formats in it, and was wondering if there was a way to count the number of cells in a column that were not coloured without going through every conditional format that I have made as there are about 20.
If there is any way to help that would be great!
Thank you
I was hoping I could solicit some help with the following conditional formatting formatting.
In one cell I have a quanity.
In another cell I have a cost.
The cost cell should equal the quanity multiplied by 10,000
So, the formula I want to put into the cond. formatting should check to make sure if the cost cell equals the quanity cell times 10,000. If yes, then no formatting.
If no, then formatting.
Thanks.
Hi there
Firstly, I'll give an image example of my worksheet:
http://i29.tinypic.com/s2u0p0.jpg
The cell that is coloured with a red fill has three conditional statments in it, which a
If highlighted cell value is greater than C2 then fill is green
If highlighted cell value is less than C2 then fill is red
If highlighted cell value is equal to C2 then fill is blank
So basically if "6" in this case is lower than "9" then fill the cell red, which it has done. But how do I copy this conditional format to all the other cells, ie: D2, F3, H2, B2, D3 and so forth (the first number of every group on every row)... when I tried to copy + paste the cell to another one I thought that Excel would automatically change the value itself of the number it was look at, but it doesn't and I copy it, it looks like this:
http://i26.tinypic.com/a3h9jb.jpg
Which is wrong because each of the copied cells is still using C2 for it's original value, instead of the value of the cell to the right of it, so looking like this would be correct (I just manually coloured all the cells for the example):
http://i27.tinypic.com/eqxyps.jpg
Is there an easy way to do this, or do I have to manually create hree conditional format rules for every single cell? (which would be very harsh, because the screenshots are just examples and the proper workbook has hundreds of columns)
Cheers!
Hi I was wondering if anyone can help me create a formula in conditional formats.
I have a series of numbers : 1 44 45 10 46 19 1 2 15 16 2
to which i would like to format subject to the numbers being with a certain range say 25 of each other. So if we take 44 then +and 25 is 19 and 69 and as 1 is outside this range then it is not formatted but 45 is and so is 44. And if we take 16 then + and  25 then 15 16 and 2 will be formatted as they fall within 25 of each other.
Can anyone suggest how i should put a formula together.
Thanks in advance
My first post = I'm working on a spreadsheet that I can input numbers into a cell and have them total below. Example:
Row C Row DE(merged) Row F
1 "/" 2
=SUM(C8:C12) "/" =SUM(F8:G12)
now, when the cell displaying "1" is left blank I have conditional formatting set to not display the / next to it but I can't get it to work for the / after the formula. (Row DE is merged for formatting purposes)
Right now cond. formatting in row DE is "formula is =ISBLANK(C8)" with white font. It seems to work for the cells that I input something but not when I have my SUM formula pop up. Any way to make it hidden but appear if the formula is greater than 0?
The reason I want them hidden is because when I print the spreadsheet I don't want the "/" to display if there is not a number inputed beside it.
Does this make sense? If not please let me know. Thanks for the help guys!
Is it possible to do a count of cells that have been coloured red through meeting a conditional formats criteria? The colour is not cell fill colour, but actual cell entry has been coloured red.
I need to create a conditional formatting,
I need to hightlight from A1:A18
In the above cells I have values from 1 to 4
So I need to fill the cell with 4 different colours.
If cell equals 1 = Green
If cell equals 2 = Yellow
If cell equals 3 = Red
If cell equals 4 = Blue
What I do is highlight cell from A1:A18, clik on 'Format', 'Conditional Formatting', I've tried using 'Cells Value is' and 'Formula is'.... but with no luck
Can anyone help
Hi
In need of a Conditional Format, or might be code!!
I have applied conditional formats to colour cells when the start and end date are entered and for weekends (see attached)....but ultimantly I would also like the calander cells to colour black if column C indicates 'A' (for approved leave) or red for 'P' (for proposed leave).
Can this be done through conditional formatting?..If not, can someone provide me with a code that I can cut and paste.
Any help would be appreciated
Ta
I need to create a conditional formatting,
I need to hightlight from A1:A18
In the above cells I have values from 1 to 4
So I need to fill the cell with 4 different colours.
If cell equals 1 = Green
If cell equals 2 = Yellow
If cell equals 3 = Red
If cell equals 4 = Blue
What I do is highlight cell from A1:A18, clik on 'Format', 'Conditional Formatting', I've tried using 'Cells Value is' and 'Formula is'.... but with no luck
Can anyone help
Hi there,
is there a way to edit and then reapply a custom format across a workbook?
For example, I have a uniform format of 0;[Red](0) for all my whole numbers but other custom formats (eg. 0%;[Red](0%) in other cells. I would like to remove the red colour for negative numbers.
I know I can edit the custom format manually but I have to do this cell by cell (or at least sheet by sheet) to affect all occurences. It's quite a big workbook and this would be a bit time consuming and very boring.
It seems like something that should be easy to do but I bet it's not!
cheers
Shane
I'm halfway through solving this problem, but have come up against a brick wall now.
To get 5 conditional formats with numbers I'm using a cell format of:
Code:
[Red][

