conditional format question

[Anthony]

Hi,
Cell E14 will contain two sets of numbers
1st set = 1p,10p,20p,30p,£1,£50,£600
2nd set = £1000,£15000,£20000

I want the colour of Cell E14 to be blue, ie
Code:
Selection.Interior.ColorIndex = 41
Selection.Font.ColorIndex = 2

if any of the values is equal to 1st set as above

or

the colour of cell E14 to be red, ie
Code:
Selection.Interior.ColorIndex = 3
Selection.Font.ColorIndex = 2

if any of the values is equal to the 2nd set as above,
Cell E14 will only contain one number at a time from any of the two sets of
numbers.
I just need the cell to be red or blue accordingly depending which number
from which set is chosen
any ideas code/and or formula ??
thanks

 

[Roger Govier]

Hi Anthony

Assuming your 1st set of numbers is held in A1 and your second set in A2

The criteria for setting Blue for cell E14 with CF, would be
=ISNUMBER(FIND(E14,A1))
and for Red would be
=ISNUMBER(FIND(E14,A2))

 

[Gary''s Student]

Try Conditional Formatting:

Formula is:
=(E14=1000)+(E14=5000)+(E14=20000)>0
and pick the red background


Similar for the small coinage

 

[Anthony]

Roger/Gary
thanks for your suggestions I'm off to try them
cheers

 

[Gary''s Student]

Your post is an excellent way to avoid excessive IFs