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


--
tino2009
------------------------------------------------------------------------
tino2009's Profile: http://www.thecodecage.com/forumz/member.php?userid=447
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=110427

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"tino2009" wrote in message
...

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


--
tino2009
------------------------------------------------------------------------
tino2009's Profile:
http://www.thecodecage.com/forumz/member.php?userid=447
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=110427

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

Bernard Liengme;395404 Wrote:
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
--
Bernard V Liengme
Microsoft Excel MVP
'Bernard Liengme' (http://people.stfx.ca/bliengme)
remove caps from email


"tino2009" wrote in message
...

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


--
tino2009

------------------------------------------------------------------------
tino2009's Profile:
'The Code Cage Forums - View Profile: tino2009'
(http://www.thecodecage.com/forumz/member.php?userid=447)
View this thread:
'Inserting a new column affects conditional formatting - The Code
Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=110427)



--
tino2009
------------------------------------------------------------------------
tino2009's Profile: http://www.thecodecage.com/forumz/member.php?userid=447
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=110427

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.

--
Biff
Microsoft Excel MVP


"tino2009" wrote in message
...

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

Bernard Liengme;395404 Wrote:
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
--
Bernard V Liengme
Microsoft Excel MVP
'Bernard Liengme' (http://people.stfx.ca/bliengme)
remove caps from email


"tino2009" wrote in message
...

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


--
tino2009

------------------------------------------------------------------------
tino2009's Profile:
'The Code Cage Forums - View Profile: tino2009'
(http://www.thecodecage.com/forumz/member.php?userid=447)
View this thread:
'Inserting a new column affects conditional formatting - The Code
Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=110427)



--
tino2009
------------------------------------------------------------------------
tino2009's Profile:
http://www.thecodecage.com/forumz/member.php?userid=447
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=110427

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


T. Valko;395664 Wrote:
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.

--
Biff
Microsoft Excel MVP


"tino2009" wrote in message
...

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

Bernard Liengme;395404 Wrote:
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
--
Bernard V Liengme
Microsoft Excel MVP
'Bernard Liengme' ('Bernard Liengme'
(http://people.stfx.ca/bliengme))
remove caps from email


"tino2009" wrote in message
...

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


--
tino2009


------------------------------------------------------------------------
tino2009's Profile:
'The Code Cage Forums - View Profile: tino2009'
('The Code Cage Forums - View Profile: tino2009'
(http://www.thecodecage.com/forumz/me...hp?userid=447))
View this thread:
'Inserting a new column affects conditional formatting - The Code
Cage Forums'
('Inserting a new column affects conditional formatting - The Code
Cage Forums'
(http://www.thecodecage.com/forumz/sh....php?t=110427))



--
tino2009

------------------------------------------------------------------------
tino2009's Profile:
'The Code Cage Forums - View Profile: tino2009'
(http://www.thecodecage.com/forumz/member.php?userid=447)
View this thread:
'Inserting a new column affects conditional formatting - The Code
Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=110427)



--
tino2009
------------------------------------------------------------------------
tino2009's Profile: http://www.thecodecage.com/forumz/member.php?userid=447
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=110427

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

--
Biff
Microsoft Excel MVP


"tino2009" wrote in message
...

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


T. Valko;395664 Wrote:
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.

--
Biff
Microsoft Excel MVP


"tino2009" wrote in message
...

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

Bernard Liengme;395404 Wrote:
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
--
Bernard V Liengme
Microsoft Excel MVP
'Bernard Liengme' ('Bernard Liengme'
(http://people.stfx.ca/bliengme))
remove caps from email


"tino2009" wrote in message
...

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


--
tino2009


------------------------------------------------------------------------
tino2009's Profile:
'The Code Cage Forums - View Profile: tino2009'
('The Code Cage Forums - View Profile: tino2009'
(http://www.thecodecage.com/forumz/me...hp?userid=447))
View this thread:
'Inserting a new column affects conditional formatting - The Code
Cage Forums'
('Inserting a new column affects conditional formatting - The Code
Cage Forums'
(http://www.thecodecage.com/forumz/sh....php?t=110427))



--
tino2009

------------------------------------------------------------------------
tino2009's Profile:
'The Code Cage Forums - View Profile: tino2009'
(http://www.thecodecage.com/forumz/member.php?userid=447)
View this thread:
'Inserting a new column affects conditional formatting - The Code
Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=110427)



--
tino2009
------------------------------------------------------------------------
tino2009's Profile:
http://www.thecodecage.com/forumz/member.php?userid=447
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=110427