MrExcel.com - Your One Stop for Excel Tips & Solutions

conditional formats(Dave can you explan the code you gave me)

Posted by steve on February 10, 2001 6:24 PM

Dave I tryed this code and it didn't work. It's exactly like the code you gave me, I changed the range to A1:E10 and it didn't work, so I changed it back and it still didn't work. Also I don't understand the second line of code, wouldn't that make it never run because the cases are all greater than 1. You also said that I need to change the limits, I'm pretty sure that has something to do with line 3 but, I don't know what I should change it to.

Thanks for all the help you've been giving me in improving my knowledge of VBa. I'm kind of new to VBa programing, but I know some C+ which makes learning VBa a little easier.


Thanks again, Steve


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Select Case Target
Case 1 To 5
Target.Interior.ColorIndex = 6
Case 6 To 11
Target.Interior.ColorIndex = 46
Case 12 To 17
Target.Interior.ColorIndex = 5
Case 18 To 23
Target.Interior.ColorIndex = 15
Case 24 To 29
Target.Interior.ColorIndex = 50
End Select
End If
End Sub

Posted by Dave Hawley on February 10, 2001 6:52 PM

Hi steve

The code should run and does on my PC.

First thing to check is to make sure the code resides in the Sheet module of the Worksheet you want effected. To do this ight click on the sheet name tab and select "View Code", this will take you to the sheet module and where the code should be.

Secondly, Make sure you are not in Design mode, this can be found under Run on the menu bar in the VBE, it should NOT be pushed in and should read "Design mode"


The second line of code:
If Target.Cells.Count > 1 Then Exit Sub

Is simply saying that if you have more than one cell selected when a change occurs then do nothing. This is optional and you can remove or comment this out if you wish.


Hope this helps, if not call again.

Dave

OzGrid Business Applications

Posted by Dave Hawley on February 10, 2001 7:00 PM


The limits I refer to are the Case statements e.g. Case 1 To 5
Target.Interior.ColorIndex = 6

This is saying if the Target (changed cell) is between or equal 1 and 5 then change the Target background colour to yellow (6)


One thing that you will need in this code as a line BEFORE the Select Case is a check to ensure the Target is a number and not text.

If Not IsNumeric(Target) Then Exit Sub

This will prevent a Run time error firing due to text entries.


Dave

  • OzGrid Business Applications

Posted by steve on February 10, 2001 7:31 PM

Thanks dave its working know