More than 3 conditional formats

kaptep

New Member
Joined
Feb 9, 2004
Messages
17
I Need to be able to format a large range of cells according to the number that they contain. Basically the cells are numbered 1-12 based on a variety of equations from other data and are updated frequently. I need the cells to be 12 different colors based on the value. Conditional formatting works great but only for 3 conditions. What is the easiest way to expand my conditions? I have read several tips for similar scenarios, but I cannot get any of the code to execute. :oops: Please give me some suggestions!!!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Easiest way I can think of is to have the code in a Worksheet_Change event on the sheet you're using.

Right click on the sheet tab and hit "view code". Toss your color criteria in a macro similar to:

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim VRange As Range
    Set VRange = Range("RangeYouWantFormated")
    
    For Each cell In Target
        If Union(cell, VRange).Address = VRange.Address Then
         
        '(toss in your format code here)

         
        End If
    Next cell
End Sub

HTH
Adam

[edit] - erm...what Iridium said (lol)
 
Upvote 0
This is a code that looks like it could be adapted to meet my needs, but I am not sure how to get it to work in the first place. I would greatly appreciate some tutoring. Thanks for your feedback thus far.

Here's a second version (see previous listing) that sets cell back to defaults if the grade is removed.


code:
--------------------------------------------------------------------------------

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

With Target
If .Column = 1 Then
Select Case Val(.Value)
Case 90 To 100 ' A
.Interior.ColorIndex = 4
Case 80 To 89 ' B
.Interior.ColorIndex = 4
Case 70 To 79 ' c
.Interior.ColorIndex = 4
Case 60 To 69 ' D
.Interior.ColorIndex = 4
Case 1 To 59 ' F
.Interior.ColorIndex = 4
Case Else 'reset to default if non of above
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 0
End Select
End If
End With
End Sub

--------------------------------------------------------------------------------




[ This Message was edited by: Nimrod on 2002-05-21 01:15 ]
 
Upvote 0
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
 
Upvote 0
:biggrin: Actually, I think I may have figured it out, but I would still love any other pointers any of you may have on this topic and a bit of an explanation of what exactly the code is saying at the beginning:

(Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Select Case Target)

What is "if not intersect is nothing then" really saying?
 
Upvote 0
Actually, I am still missing a piece. I got it to work if I go type in a number in one of the cells, but the value of the cell is based on an equation. For example: =IF(ISERROR(N44)=TRUE,O43,N44). In this particular case the cell in question is equal to 1, but the next time I update the spreadsheet the cell may be equal to 3. How do I get the formatting to change accordingly? I really appreciate the help!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top