Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default Inserting a new column affects conditional formatting


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

  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,104
Default Inserting a new column affects conditional formatting

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


  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default Inserting a new column affects conditional formatting


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

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Inserting a new column affects conditional formatting

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



  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default Inserting a new column affects conditional formatting


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



  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Inserting a new column affects conditional formatting

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



  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default Inserting a new column affects conditional formatting


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;395734 Wrote:
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'
('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'
('The Code Cage Forums - View Profile: tino2009'

(http://www.thecodecage.com/forumz/me...p?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'
('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'
('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

  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Inserting a new column affects conditional formatting

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.

--
Biff
Microsoft Excel MVP


"tino2009" wrote in message
...

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;395734 Wrote:
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'
('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'
('The Code Cage Forums - View Profile: tino2009'

(http://www.thecodecage.com/forumz/me...p?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'
('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'
('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



  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,346
Default Inserting a new column affects conditional formatting

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


"tino2009" wrote:


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;395734 Wrote:
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'
('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'
('The Code Cage Forums - View Profile: tino2009'

(http://www.thecodecage.com/forumz/me...p?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'
('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'
('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


  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default Inserting a new column affects conditional formatting


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

Shane Devenshire;396442 Wrote:
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



[/color]


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional formatting affects text as well as values SueG Excel Discussion (Misc queries) 7 March 2nd 09 03:12 PM
Formatting cells in a column with conditional formatting? shamor Excel Discussion (Misc queries) 8 May 19th 08 10:11 PM
Excel - unHiding a column affects columns in other worksheets? John Powell Excel Worksheet Functions 3 February 10th 07 01:23 AM
Conditional Formatting when inserting a row zahoulik Excel Worksheet Functions 2 January 7th 06 04:01 PM
Conditional Formatting Reference / Inserting Rows Werner Rohrmoser Excel Worksheet Functions 1 September 9th 05 02:27 PM


All times are GMT +1. The time now is 08:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"