highlight a column

[Mitchell_Collen via OfficeKB.com]

Hi Everyone.
Please help me. I am trying to figure out how to write a conditional
formatting type macro. My spreadsheet contains a schedule of 50+ employees
and days of month listed. I need the macro to auto-highlight yellow the
active cell's column if the total of the active cell's column exceeds 200. if
not the cell should remain unchanged. The worksheet looks similar to this:

Name 9/1/07 9/2/07 9/3/07 9/4/07 ...
Mary 0 8 8 8
Jane 8 8 0 0
Robert 8 0 8 8
...

Total: 201 200 230 200

I am using excel 97.

Thanks, Misty

 

[Guest]

Select the entire column
I assume your total is on row 14. You can change it as per actual total line.
Go to Conditional formatting and select 'formula is'
type in this formula '=IF($B$14>200,TRUE,FALSE)'
select the color to hightlight.

This will highlight the entire column, when value in B14 is more than 200.
Please adjust this formula as per your worksheet.

HTH,

 

[Mitchell_Collen via OfficeKB.com]

Thanks for your reply.
The row willl change as more employees are added and deleted. Do you know how
to sum a column that is dynamic?
Thanks, Misty

Select the entire column
I assume your total is on row 14. You can change it as per actual total line.
Go to Conditional formatting and select 'formula is'
type in this formula '=IF($B$14>200,TRUE,FALSE)'
select the color to hightlight.

This will highlight the entire column, when value in B14 is more than 200.
Please adjust this formula as per your worksheet.

HTH,

Hi Everyone.
Please help me. I am trying to figure out how to write a conditional

[quoted text clipped - 14 lines]

Thanks, Misty

 

[Guest]

I think then instead of using absolute row, column, you can try to use a
Named Range. Whenever, your total row changes, change the named range and I
hope it should work.

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!

Thanks for your reply.
The row willl change as more employees are added and deleted. Do you know how
to sum a column that is dynamic?
Thanks, Misty

Select the entire column
I assume your total is on row 14. You can change it as per actual total line.
Go to Conditional formatting and select 'formula is'
type in this formula '=IF($B$14>200,TRUE,FALSE)'
select the color to hightlight.

This will highlight the entire column, when value in B14 is more than 200.
Please adjust this formula as per your worksheet.

HTH,

Hi Everyone.
Please help me. I am trying to figure out how to write a conditional

[quoted text clipped - 14 lines]

Thanks, Misty

 

[Mitchell_Collen via OfficeKB.com]

I am confused about how to name a range. Do you mean naming a range 'total'
and then referring back to the formula where the value is = 'total' and the
column number is the active column number?
I am really new to this.
Thanks, Misty

I think then instead of using absolute row, column, you can try to use a
Named Range. Whenever, your total row changes, change the named range and I
hope it should work.

Thanks for your reply.
The row willl change as more employees are added and deleted. Do you know how

[quoted text clipped - 16 lines]