Conditional Formatting Not working (xls 2003)

G

Guest

Hello:
What could possibly be keepping the conditional formatting from working. I
have tried a very simple format: if text in col D = "hello", backfill is
light yellow:

I have used the "format, conditional formatting" dialogue using the
"forumula is" $D2 = "string". It isn't working. Any ideas?

=""""""
 
D

David McRitchie

When you entered that specific formula
=$D2 = "string".
was D2 the active cell, and did you include the equal sign ?.
The cells that are selected are the cells that get colored, the formula
is based on the active cell, in this formula any cell on row 2 could be
the active cell, in fact with that formula you could color the entire
row if you wanted to. Note normally if you select all cells or a
column(s) a cell on the first row would actually be selected is the
colored row off by one row..

Conditional Formatting:
http://www.mvps.org/dmcritchie/excel/condform.htm
 
G

Gord Dibben

Try

Select Column D and CF

Formula is =$D1= "hello" rather than "string"

Gord Dibben Excel MVP
 
G

Guest

hello David:
Something's up. Actually i found your Select Case code in another thread
and it isn't working. Only the cell of the first case is being
color-coded--not the row per the target cell. I have another case, and it
isn't working at all. The conditions are looking for a string in Column F.
I found the numeric value of the color for the 2nd Case by using a macro (it
is #20). What could possibly be wrong? I'm running xls 2003;
 
D

David McRitchie

Did you look at my page on Conditional Formatting, because it
sounds like you are one row off, like I indicated.

Another possibility, Conditional Formatting formulas are checked
from the top down C.F. 1 then 2 then 3
if you get a true condition on the first one the other two will not
be checked.

What you need to know for C.F. is what columns to you want to be
colored by C.F. Those are the columns that must be selected when you
create your C.F.

You also need to create your formula based on what the active cell is.
That takes care of that row. The other rows are processed like you did
a fill down.

You can test your formulas in three helper columns on each row
Enter each of your formulas across the three helper columns.
The idea of C.F. is to return True or False. If you do not get the
correct indication then your C.F. is incorrect. Use $ in font of the
column part of the address so that you can use a different cell in your
formula.

The above should tell you if your formulas are working or not, but you
might also check the following if the data is not what it seems to you.
http://www.mvps.org/dmcritchie/excel/formula.htm#debugformat
 
E

Eric Barber

Bettergains said:
I have used the "format, conditional formatting" dialogue using the
"forumula is" $D2 = "string". It isn't working. Any ideas?
do you mean
=$D2="hello"
with format/pattern set to a yellow?
 
G

Guest

Hello David: thanks for your response. Here is the code exactly (preserving
your credist~). This works to the extent that any cell in Col D with "tcr is
later" gets yellow highligted, but not the row, and the second conditions
(highlight in light blue) doesn't get implemented. So apparently is does
loop through the 100+ rows. Not sure why it isn't cycling through the Select
Case statements.

Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2004-09-26, programming, Case -- Entire Row
' http://www.mvps.org/dmcritchie/excel/event.htm#case_row
If Target.Column <> 4 Then Exit Sub 'Column D is column 6
If Target.Row = 1 Then Exit Sub
Application.EnableEvents = False 'should be part of Change macro
Select Case LCase(Target.Value)
Case "tcr is later"
Target.EntireRow.Interior.ColorIndex = 19
Case "tcr not set"
Target.EntireRow.Interior.ColorIndex = 20
Case Else
Target.EntireRow.Interior.ColorIndex = xlColorIndexAutomatic
End Select
Application.EnableEvents = True 'should be part of Change macro
End Sub
 
G

Guest

Hi Eric: thanks for your response. Here is the Code:

This works to the extent that any cell in Col D with "tcr is later" gets
yellow highligted, but not the row, and the second condition (highlight in
light blue) doesn't get implemented. So apparently itdoes loop through the
100+ rows. Not sure why it isn't cycling through the Select Case statements.
 
G

Guest

Hello Gord: thanks for your response. Here is the code exactly (preserving
David Mc's credist~). This works to the extent that any cell in Col D with
"tcr is later" gets yellow highligted, but not the row, and the second
conditions (highlight in light blue) doesn't get implemented. So apparently
is does loop through the 100+ rows. Not sure why it isn't cycling through
the Select Case statements.

Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2004-09-26, programming, Case -- Entire Row
' http://www.mvps.org/dmcritchie/excel/event.htm#case_row
If Target.Column <> 4 Then Exit Sub 'Column D
If Target.Row = 1 Then Exit Sub
Application.EnableEvents = False 'should be part of Change macro
Select Case LCase(Target.Value)
Case "tcr is later"
Target.EntireRow.Interior.ColorIndex = 19 'highlight light yello
Case "tcr not set"
Target.EntireRow.Interior.ColorIndex = 20 'highlight light blue
Case Else
Target.EntireRow.Interior.ColorIndex = xlColorIndexAutomatic
End Select
Application.EnableEvents = True 'should be part of Change macro
End Sub
 
D

David McRitchie

if you abnormally terminated you will have to run a regular macro
to reenable events so that the event macro will work.
http://www.mvps.org/dmcritchie/excel/event.htm#problems

For the macro:
you can type this into the Intermediate Window (Ctrl+G) of the VBE
first see if you had a problem
Application.EnableEvents
then if it shows False fix it
Application.EnableEvents = True

For your Conditional Formatting: which would be the best solution
-- Select Cell D1 (or any cell on ROW 1)
-- Ctrl+A to select all cells on workbook (see shortx2k.htm#ctrla if you have Excel 2003)
-- Format, Conditional Formatting
Condition 1: =TRIM($D1)="tcr is later" or use =ISNUMBER(SEARCH("tcr is later",TRIM($D1)))
Condition 2: =TRIM($D1)="tcr not set" or use =ISNUMBER(SEARCH("tcr not set",TRIM($D1)))

I put your TRIM around the $D1 so that if you have multiple spaces they will be
reduced. You could effect the same thing and better for constants by running the TRIMALL macro
in http://www.mvps.org/dmcritchie/excel/join.htm#trimall
if the data is your problem, read about checking your data LEN etc. . .
My page on Conditional Formatting is
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Don't really see the need to give the same reply to each person in the thread.
Please use your name when you post to newsgroups.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top