Highlight selected cells if text is in certain column

[TJV]

I've been sorting through the message boards and I can't quite find a
subject to address this issue. I'm working with Excel 97.

I have a spreadsheet that I'm trying to get to highlight certain cells
in the corresponding row based on the text typed in the cells of
column "V".

Column "V" is titled "Delete". Users can either type a "x" or leave it
blank. If they type an "x" I want it to black out certain cells on the
corresponding row. If they leave it blank or delete the "x" to make it
blank, then I want it to maintain its original formatting.

Here's a macro that I found in the newsgroups that gets me partially
to where I need to be. This macro blacks out the appropriate cells
when a cell in column "V" is selected, which is great, but when I
select another cell in column "V", it takes the black off that row and
blacks out the row where the cell is selected. I want the row to black
out the appropriate cells when an "x" is typed rather than just
selecting the cell. Here's the macro:

Option Explicit
Dim LastRange As Range
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim thisRow As Long

If Target.Column = 22 Then
thisRow = Target.Row
If Not LastRange Is Nothing Then
LastRange.Interior.ColorIndex = 0
End If
Set LastRange = Range(Cells(thisRow, 22), Cells(thisRow, 46))
LastRange.Interior.ColorIndex = 1
ElseIf Not LastRange Is Nothing Then
If Intersect(Target, LastRange) Is Nothing Then
LastRange.Interior.ColorIndex = 0
End If
End If
End Sub

The second part of my problem is that my spreadsheet has certain
columns which are preformatted to color some columns green. I have a
macro that runs at the beginning of formatting which selects certain
columns and colors them green (columns AM:AO). Yet when I black out
the row by selecting a cell in column "v" (using the above macro),
then I lose the green formatting on my columns for the row that was
blacked out. How do I get it to revert back so that it has green on
those columns again? Is there like an "undo" formatting if I have an
"x" in the column and then later delete the "x" or do I have to write
code so that if it is "" that I need to color certain cells in that
row green again?

I've seen macros to black out the whole row, but I don't want to do
that because I have info in columns A:U that will be hidden from the
user but that I will need to see. If they are blacked out by the user
typing an "x" then I can't see this data. That's why I only want to
black out certain cells on that row.

Help! I've been using the message boards for a while and have found
some great stuff to use but I can't find something to address this.

Thanks!

 

[Tom Ogilvy]

You need to drop the macro approach and use Conditional formatting.

Select the cells you want blacked out

Then go to Format=>Conditional formatting
Change Cell Value is to
Formula is

in the text box put in. Assume we are on Row 2 or you have select multiple
rows (and columns) but the activecell is in row 2

=$V2="X"
then click on format and select the pattern tab. Click on the black color.

Then OK your way out.

When an X is in column V, those cells in that row will appear black. When
you remove the X, they will return to their original formatting.

 

[neowok]

i have a similar problem but slightly more complicated.

i want to turn the selected cells a certain colour of the date i
column H is in the past, i.e. before today.

the date format in the H column is custom dd-mmm-yy format.

any ideas

 

[Frank Kabel]

Hi
you may use conditional format:
- select your cells
- goto 'Format - conditional Format'
- enter the formula
=$H1<TODAY()

 

[neowok]

seems to work except it sets the entire table to the colour i specif
based on one row at the top, rather than setting each row based o
whether the H column in THAT row is <today or not.

any way to easily specify it should do it like this without having t
set conditional format individually on every row in the tables

 

[Frank Kabel]

Hi
just select all rows you require (starting in row one) and enter the
formula
=$H1<TODAY()
the row_index will change automatically

 

[neowok]

its working now but i might not go with this after all because usin
today its a volatile function and will mean every time the workbook i
opened, users will be prompted to save it due to changes (especially a
this is going on the intranet and will only be updated once a week).

Thank