Possibly conditional format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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>=14,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
 
Hi Aristotle

Thanks for your response. Unfortunately, it didn't work out.

Many thanks for your reply though.

Ellie
 
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 se
Format>Patterns 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 value
listed above.

HTH

Bruc
 
Back
Top