Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Conditional Format: If A Cell Equals One Of Four Numbers

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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

Helpful Excel Macros

Format Cells in The Number (Numerical) Number Format in Excel
- This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Format Cells as a Scientific Number in Excel Number Formatting
- This free Excel macro formats selected cells in the Scientific number format in Excel. This means the cell will be put
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 O6-O26 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 set-up 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 re-edit 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 1-20, 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.excel-jeanie-html.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 what-so-ever 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/excel-cond...ting-limit.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 re-apply 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][