Conditional Formating Limits

  • Thread starter Thread starter DMP
  • Start date Start date
D

DMP

Hello,

Yesterday I got a relpy to a post about conditional
formating that advised me to go to this website
http://www.mvps.org/dmcritchie/excel/event.htm#case.

I read the information on event macros and found it useful
but in trying to apply it for my purposes, I am having
dificulties. So let me try to explain it again...

I have a column in my data sheet that tracks 5 different
error messages. What I would like to do is say if cel H2
contains "Bad Formula" then highlight it pink. Or if it
says "Msg Not Recorded" then highlight it green etc etc
etc...Keep in mind that the H column contains a validation
list to pull down from...so if I pull down this statement
or that statement, I want the cel to change colors this
gives me an instant view of how many of these messages I
am getting so I can look for patterns of increase or
decrease over time...So it there a VBA script that will
help me do this since I can't use conditional formating
due to it only letting me have three conditons...

Thanx
DMM
 
Hi
the event procedure described on this page is just what you need. What
exact problems did you encounter?
 
I am not understanding the language used and how to apply
it...The site talks about case...what does this
mean...Would case be whatever text I am using to trigger
the color change...for example..would (case 1) be
"Bad Formula"??

How would I write this??

Thank you so much..

DMP
 
Hi
o.k. if you like please post all your conditions and the desired format
and I'll post a procedure for you
 
something about give a fish or teach how to fish. OP should be able to
figure it out.
 
Hi Don
agreed, but maybe something to get him started. so maybe inserting the
first case statements and lieving the rest for him to figure out :-)
 
WOW....thank you very much....

Here are the conditions

Range K2:K2232

Bad Formula = Pink
Duplicate Record = Yellow
Msg Not Recorded = Green
Zero Byte File = Light Blue
Java Error = Orange

Again thank you very much

DMP
 
Hi
so as a starting point use the macro find below. Some remarks:
- this macro has to go in your worksheet module (as it is an event
procedure). To get there right-click on your tabname, select 'Code' and
paste the code in the appearing VBA editor window
- For the other colors (the correct colorindex code see:
http://www.mvps.org/dmcritchie/excel/colors.htm
- you have to add the remaining case statements - should be easy :-)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("K2:K2232")) Is Nothing Then
Exit Sub
End If

Select Case Target.Value
Case "Bad Formula":
Target.Interior.ColorIndex = 7
Case "Duplicate Record"
Target.Interior.ColorIndex = 6
'more case statements in the same order
End Select

End Sub
 
Okay so here are all of my conditions and yes they do work
but I have a question....what modifications do I need to
make so that when one of the conditions is pulled down,
the color changes automatically. AS it is now, the color
changes after you choose form the pulldown and then click
the cel. Also, lets say you want to change what is in the
cel or even make the cel blank...the clor will not change
nor will it delete if the cel is blank....


If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("K2:K2232")) Is Nothing Then
Exit Sub
End If

Select Case Target.Value
Case "Bad Formula":
Target.Interior.ColorIndex = 7
Case "Duplicate Record"
Target.Interior.ColorIndex = 6
Case "Java Error"
Target.Interior.ColorIndex = 46
Case "Zero Byte File"
Target.Interior.ColorIndex = 33
Case "Msg Not Recorded"
Target.Interior.ColorIndex = 10

'more case statements in the same order
End Select

End Sub
 
Hi
if you use the drop down boxes from 'Data - Validation'then it should
work without a problem. At least it works for me with Excel 2003
(Excel97 has some problems with this). That kind of pulldowns are you
using?
 
Yes this is a validation list....I can pull down the text
but the color wont change unless I click the cel...and if
I remove the text or change the text the color will not
change nor will go back to blank....

D
 
Hi
change your code to
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("K2:K2232")) Is Nothing Then
Exit Sub
End If

Select Case Target.Value
Case "Bad Formula":
Target.Interior.ColorIndex = 7
Case "Duplicate Record"
Target.Interior.ColorIndex = 6
Case "Java Error"
Target.Interior.ColorIndex = 46
Case "Zero Byte File"
Target.Interior.ColorIndex = 33
Case "Msg Not Recorded"
Target.Interior.ColorIndex = 10

Case Else
Target.Interior.ColorIndex = xlColorIndexNone

'more case statements in the same order
End Select

End Sub


if this still does not work you may email me your file
 
Helleo Frank...

Okay I made the modification and this is the result:

When I choose from the validation list the text
displays "Bad Formula" I have to then re-click the cell
for it to change to the specified color.
If I go back and remove the text, I then have to re-click
the cell in order for the color to default to blank. The
goal is to have to color appear once the text is chosen
and if no text is in the cell for the cell to be blank
therfore eleminating the need to click twice...

Thanx..DMP


I have included the modified code

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("K2:K2232")) Is Nothing Then
Exit Sub
End If

Select Case Target.Value
Case "Bad Formula":
Target.Interior.ColorIndex = 7
Case "Duplicate Record"
Target.Interior.ColorIndex = 6
Case "Java Error"
Target.Interior.ColorIndex = 46
Case "Zero Byte File"
Target.Interior.ColorIndex = 33
Case "Msg Not Recorded"
Target.Interior.ColorIndex = 10
Case Else
Target.Interior.ColorIndex = xlColorIndexNone

'more case statements in the same order
End Select

End Sub
 

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

Similar Threads

Conditional Formatting 1
Excel Excel Conditional Format 2
Conditional Formatting - Comparative Versus Other Cells 4
Conditional Formatting Help 1
Conditional Format a dynamic list in Excel 2007 1
I have more than 6 conditions when using conditional formatting? 3
Excel Conditional Formatting 1
Conditional formatting on 2nd cell 5

Back
Top