Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Possibly conditional format
I have an Excel 2000 spreadsheet with Column G having random numbers from 1-20.
I am trying to find a way whereby:- 1) Numbers 1,3,4,8,10-12,14-19 highlight the cells in red, and 2) Numbers 2,5-7,9,13 and 20 are highlighted in orange Is there a particular formula I may be able to use in conditional format, or would another process be more suited to this task. Many thanks. |
#2
|
|||
|
|||
One way is to use Format|conditional formatting.
With column G selected and G1 the activecell, use Format|conditional formatting. Formula is: =ISNUMBER(SEARCH("."&G1&".",".1.3.4.8.10.11.12.14. 15.16.17.18.19.")) Give it a pattern of red under the format button. (similarly for the orange values) ==== But I think I would create a new worksheet. put those values in column A 1 3 4 8 10 11 12 .... Give that column a nice range name (insert|name|define MyRedCol) And use a formula like: =countif(myredcol,g1)0 And same kind of thing for myOrangeCol Then I could just change those values in Sheet2 whenever I wanted to. Ellie wrote: I have an Excel 2000 spreadsheet with Column G having random numbers from 1-20. I am trying to find a way whereby:- 1) Numbers 1,3,4,8,10-12,14-19 highlight the cells in red, and 2) Numbers 2,5-7,9,13 and 20 are highlighted in orange Is there a particular formula I may be able to use in conditional format, or would another process be more suited to this task. Many thanks. -- Dave Peterson |
#3
|
|||
|
|||
Hi,
Select e.g. A1:A100 Go to Format-Conditional Format - Formula The first condition formula should appear as follows: =IF(OR(A1=1,A1=3,A1=4,AND(A1=10,A1<=12),AND(A1=1 4,A1<=19))) choose the desired format... The second condition formula should appear as follows: =IF(OR(A1=2,AND(A1=5,A1<=7),A1=9,A1=13,A1=20)) choose the desired format... I haven't tested this so hope it's right. Regards, A "Ellie" wrote: I have an Excel 2000 spreadsheet with Column G having random numbers from 1-20. I am trying to find a way whereby:- 1) Numbers 1,3,4,8,10-12,14-19 highlight the cells in red, and 2) Numbers 2,5-7,9,13 and 20 are highlighted in orange Is there a particular formula I may be able to use in conditional format, or would another process be more suited to this task. Many thanks. |
#4
|
|||
|
|||
Hi Aristotle
Thanks for your response. Unfortunately, it didn't work out. Many thanks for your reply though. Ellie "aristotle" wrote: Hi, Select e.g. A1:A100 Go to Format-Conditional Format - Formula The first condition formula should appear as follows: =IF(OR(A1=1,A1=3,A1=4,AND(A1=10,A1<=12),AND(A1=1 4,A1<=19))) choose the desired format... The second condition formula should appear as follows: =IF(OR(A1=2,AND(A1=5,A1<=7),A1=9,A1=13,A1=20)) choose the desired format... I haven't tested this so hope it's right. Regards, A "Ellie" wrote: I have an Excel 2000 spreadsheet with Column G having random numbers from 1-20. I am trying to find a way whereby:- 1) Numbers 1,3,4,8,10-12,14-19 highlight the cells in red, and 2) Numbers 2,5-7,9,13 and 20 are highlighted in orange Is there a particular formula I may be able to use in conditional format, or would another process be more suited to this task. Many thanks. |
#5
|
|||
|
|||
First, format G1:G20 as Red fill (all cells red) Set conditional formatting in G1 to: Condition 1 Formula Is: =OR($G1=2,$G1=5,$G1=6,$G1=7,$G1=9,$G1=13,$G1=20) and set FormatPatterns to Orange copy this formatting down to G20 using the Format Painter. All cells will have Red fill unless it contains one of the six values listed above. HTH Bruce -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=394982 |
#6
|
|||
|
|||
Did you change those column A references to column G?
Ellie wrote: Hi Aristotle Thanks for your response. Unfortunately, it didn't work out. Many thanks for your reply though. Ellie "aristotle" wrote: Hi, Select e.g. A1:A100 Go to Format-Conditional Format - Formula The first condition formula should appear as follows: =IF(OR(A1=1,A1=3,A1=4,AND(A1=10,A1<=12),AND(A1=1 4,A1<=19))) choose the desired format... The second condition formula should appear as follows: =IF(OR(A1=2,AND(A1=5,A1<=7),A1=9,A1=13,A1=20)) choose the desired format... I haven't tested this so hope it's right. Regards, A "Ellie" wrote: I have an Excel 2000 spreadsheet with Column G having random numbers from 1-20. I am trying to find a way whereby:- 1) Numbers 1,3,4,8,10-12,14-19 highlight the cells in red, and 2) Numbers 2,5-7,9,13 and 20 are highlighted in orange Is there a particular formula I may be able to use in conditional format, or would another process be more suited to this task. Many thanks. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keep conditional format when "show pages" from Pivot table | Excel Discussion (Misc queries) | |||
How do I do a conditional format for numbers | Excel Worksheet Functions | |||
Office2000: Conditional format behaves strangely | Excel Discussion (Misc queries) | |||
copy conditional format as ACTUAL format | Excel Discussion (Misc queries) | |||
Copying a conditional format | Excel Worksheet Functions |