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.

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

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.

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.

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

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