Horizontal Conditional Formatting?

S

Saxman

If I had a cell in column M with the value 1, (in this case, cell M20),
could I highlight the cell colour in row 20 from columns A onward and
any other rows where the value 1 featured in column M?

TIA
 
B

Bernard Liengme

Select the range to have conditional format (starting with row 20) and use
Formula is =$M20 =1 and then format
best wishes
 
K

kassie

Select range A20:M20. Click on Format, Conditional Formatting. Change Cell
Value is to Formula is, and insert =$M$20=1. Click on format, select
Patterns, and select the colour of your choice.
 
S

Saxman

kassie said:
Select range A20:M20. Click on Format, Conditional Formatting. Change Cell
Value is to Formula is, and insert =$M$20=1. Click on format, select
Patterns, and select the colour of your choice.
Click to expand...

This works, but only if I type a new value in M20, not when a value exists.

When I paste new data into columns A:J, this affects the formatting as well.

Could this be done for all cells in M?

=$M$1:500=1??
 
E

Earl Kiosterud

Saxman,

You aren't clear on your requirements. I think you want a part of a row to
change color when M of that row is 1. If so:

1. Select the range to be conditionally formatted. Note the row that the
active (white) cell of your selection is.

2. Format - Conditional formatting - Formula is: =$M20=1 (in this case the
active cell was in row 20. Change formula as necessary).

3. Click "Format" and select the desired formatting.

4. OK your way out.
 
S

Saxman

Earl said:
Saxman,

You aren't clear on your requirements. I think you want a part of a row to
change color when M of that row is 1. If so:
Click to expand...

Whenever a value in column M =1, I would like the corresponding/adjacent
line/s highlighted. The value 1 would appear in several cells in row M.

Thanks for the feedback.
 
K

Ken Johnson

Hi Saxman,

Select the whole sheet by clicking the empty square above row 1's label
and to the left of column 1's label.
Go Format|Conditional Formatting...
Use...

=$M1=1

in the Formula Is box.
Apply you highlighting format then click OK.

Ease up on the free food and drink;-)

Ken Johnson
 
S

Saxman

Ken said:
Hi Saxman,

Select the whole sheet by clicking the empty square above row 1's label
and to the left of column 1's label.
Go Format|Conditional Formatting...
Use...

=$M1=1
Click to expand...

That works fine, but only when I enter new data.

MY data is copied from another source and pasted as 'text special' in
columns A:J. All calculations are done in the remaining columns.

Pasting the data has no effect on the formatting if cells in the M
column contain 1, unless I type in 1 after the data has been pasted.
Probably because cell in the M column contains the following calculation.

=LEFT(H20,FIND("/",H20,1)-1)

(For cell M20).

It extracts the left digit from 1/12, i.e., finished 1st from 12 runners.

I can get over by typing 1 as above. Quicker than doing it by
highlighting rows etc.
 
R

Ragdyer

If only the "whole story" would be REVEALED in the OP !

That formula of yours produces a TEXT value!

SO ... either change the formula in the cell, or the formula in the
Conditional format, so that you're comparing apples to apples ... text to
text ... or ... numeric to numeric.

Returns text:
=LEFT(H20,FIND("/",H20,1)-1)

Returns numeric:
=--LEFT(H20,FIND("/",H20,1)-1)

Evaluates text:
=$M1="1"

Evaluates numeric:
=$M1=1

Change whatever you wish, just as long as they return the same type of
value.
 
S

Saxman

Ragdyer said:
Evaluates text:
=$M1="1"
Click to expand...

That works a treat!

I have learnt a lot. So simple, but very important.

I have to convert the worksheet to text, other wise Excel converts data
like 9-2 and 5/1 to text.

Thanks so much.
 
R

RagDyeR

You're welcome, and appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Ragdyer said:
Evaluates text:
=$M1="1"
Click to expand...

That works a treat!

I have learnt a lot. So simple, but very important.

I have to convert the worksheet to text, other wise Excel converts data
like 9-2 and 5/1 to text.

Thanks so much.
 
Top