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

Select the range to have conditional format (starting with row 20) and use
Formula is =$M20 =1 and then format
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Saxman" wrote in message
...
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

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.

"Saxman" wrote:

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

kassie wrote:
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.

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??

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.


--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Saxman" wrote in message
...
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

Earl Kiosterud wrote:
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:

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.

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

Ken Johnson wrote:

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

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.

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Saxman" wrote in message
...
Ken Johnson wrote:

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

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.

Ragdyer wrote:

Evaluates text:
=$M1="1"

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.

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

Regards,

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

"Saxman" wrote in message
...
Ragdyer wrote:

Evaluates text:
=$M1="1"

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.

Saxman wrote:

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

I meant to a date format.