kaptep said:
Also, I tried the code you posted on another topic as listed below. I am just missing a basic concept of VBA. So I have numbers between 1-30 in cells a:1-a:10. I paste the code in as instructed, but none of the formatting changes and the only thing I get is that when I try to change one of the numbers I now get an error message. WHAT AM I NOT GETTING HERE???
code:
--------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'Getting past Conditional Formattings 3 Criteria Limit
'This code must be placed in the Private Module of the Worksheet. _
To get there right click on the sheet name tab and select "View Code".
'Excel's very handy Conditional Formatting unfortunately only allows up to 3 conditions. _
The method below gets around this limit. It is set to work on A1:A10 only. event.
Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20
icolor = 53
Case 21 To 25
icolor = 15
Case 26 To 30
icolor = 42
Case Else
'Whatever you want
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
Hi kaptep, you asked quite a few questions here so I'll just answer this one first then you can specify the problem again from there. Im not sure if you understand the concept of the above code so Ive included a brief bit on the worksheet_change event.
1. Worksheet_Change event
Some macros are designed to run when you ask them to run (by selecting Tools|Macro|Macros) while others run automatically based upon an event. The above code is the second type of macro and it needs to be stored in the Sheet module as opposed to stand alone module. If its not in there, then the code wont run.
Right click the sheet concerned and select View Code then paste the code in.
2. What does If Not Intersect etc mean?
The worksheet_change event runs every time you change the value in a cell (but not formatting). So this means that the code will run on every change, even though you may only want to format certain cells. This will slow your workbook down as its doing unnecessary changes.
To solve this you first check whether the cell that was changed is in the area where you want to do formatting. Thats what this line does.
The
Intersect(Target, Range("A1:A10")) Is Nothing part means see if the target (I'll explain that next) doesnt intersect with a particluar range, in this case A1:A10. We actually want to see if it
does intersect, so we use the If Not in front - a double negative type of operation.
In fact this line has an error as target should actually be Range(Target.Address) but this needs to be changed anyway as per my next comment
3. Target & Flaw In This Code
I mentioned the value Target before. Target is an argument automatically included with the Worksheet_Change event and represents the range of cells that were changed in the last action by the user.
Target can refer to multiple cells (like A1:A10) or can be one cell. You would have multiple cells changed if you copied and pasted for example. With the above code it does not cater for multiple cells being changed, so will return an error if you change more than one cell.
To fix this, use this code instead. Note that a new variable called c is created and used which represents an element(cell) in the range.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Getting past Conditional Formattings 3 Criteria Limit
'This code must be placed in the Private Module of the Worksheet. _
To get there right click on the sheet name tab and select "View Code".
'Excel's very handy Conditional Formatting unfortunately only allows up to 3 conditions. _
The method below gets around this limit. It is set to work on A1:A10 only. event.
Dim icolor As Integer, c
For Each c In Target
If Not Intersect(Range(c.Address), Range("A1:A10")) Is Nothing Then
Select Case c.Value
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20
icolor = 53
Case 21 To 25
icolor = 15
Case 26 To 30
icolor = 42
Case Else
icolor = 0 'no color
End Select
c.Interior.ColorIndex = icolor
End If
Next c
End Sub
If you want me to explain anything more let me know.
hth
