Inserting a new column affects conditional formatting

[tino2009]

Hey,

I have got conditional formatting setup on a sheet that highlights a
row based on the value in two columns (K and L). The formula for this
is;

=AND(INDIRECT("L"&ROW())="No", INDIRECT("K"&ROW())="Sent")

I have got 6 varieties of this, and it all works well and highlights
each row correctly.

However, I need to insert a new column before K - and when I do I lose
the conditional formatting. I have tried manually altering the formula
as;

=AND(INDIRECT("M"&ROW())="No", INDIRECT("L"&ROW())="Sent")

but this does not work properly for the 'AND' rules. It does work ok on
=INDIRECT("L"&ROW())="Sent" though.

Is there an easy way to insert a new column without messing up the
formatting?

Thanks

 

[Bernard Liengme]

In general a common side effect of INDIRECT is that inserting row/columns
can cause havoc because the reference cannot change when the insert is made.
It is the nature of INDIRECT and indeed some people reply on this feature
best wishes

 

[tino2009]

Is there an alternative to INDIRECT that you may suggest, that would
achieve the same result?

 

[T. Valko]

Why are you using INDIRECT?

Can't you just use A1 references?

=AND(L1="No", K1="Sent")

If you insert a new column before column K the references will automatically
adjust.

 

[tino2009]

I have just tried that, and it doesn't seem to highlight the row at all.

 

[T. Valko]

Tell us *exactly* which cells you want to highlight and based on what
condition.

 

[tino2009]

Users will input information into the sheet. When they enter "No" in
column M, and "Sent" in column L, the whole row should fill red.

I have this working, but on column K and L. I just need to insert a new
column before K, and I do not seem able to replicate the correct rules -
even in a brand new sheet using the exact same rules that are currently
working! wko

Thanks for your time.

 

[T. Valko]

We need to narrow down what cells you're talking about.

Try explaining it in these terms:

The user will enter data in the range L2:M100. When the user enters "Sent"
and "No" on the same row in column L and M, I want to apply conditional
formating to that row from column A to column M.

 

[Shane Devenshire]

Hi,

To conditionally format your cell(s):

In 2003:
1. Select the cells you want to format - Here lets say A1:Z100
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=AND($L1="Sent",$M1="No")
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=AND($L1="Sent",$M1="No")
5. Click the Format button and choose a format.
6. Click OK twice

If this helps, please click the Yes button.

 

[tino2009]

Thanks done it!!!! Many thanks Bg:Bgr):laugh

Hi,

To conditionally format your cell(s):

In 2003:
1. Select the cells you want to format - Here lets say A1:Z100
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=AND($L1="Sent",$M1="No")
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=AND($L1="Sent",$M1="No")
5. Click the Format button and choose a format.
6. Click OK twice

If this helps, please click the Yes button.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

[/QUOTE]