VBA Cond. Format mod

[Gareth Brown]

G'day, need some help modding this worksheet code to suit my needs (thanks nimrod for the template)

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$A$1" Then
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 41 'blue
Case 3: .Interior.ColorIndex = 53 'brown
Case 4: .Interior.ColorIndex = 39 'purple
Case 5: .Interior.ColorIndex = 46 'orange
Case Else
.Interior.ColorIndex = 0
End Select
End If
End With
End Sub

Essentially I need Range A1:C4 (or whatever) to change colour based on the value of A5 (or whatever)

If the code above is all mashed, it's from the thread entitled "Conditional Formating" (with the spelling mistake)

Cheers, GB
This message was edited by Gareth Brown on 2002-11-04 21:13

 

[kieran]

You do not need to resort to coding events.
Use the conditional format option under the format menu.

Select the range you want to format,
use the 'formula is' option and specify the values that you want a5 to take for colour,
et the format (patterns) using the format button.
Repeat the above for all of the possible values of a5.

 

[Gareth Brown]

Nah mate, conditional format is limited to 3 conditions - you can have many different ones doing it this way

By the way, the range is full of formulas that i don't want to display if cell a5 fails and i'd rather not make them any more complicated (if possible)
This message was edited by Gareth Brown on 2002-11-05 01:33

 

[Derek]

Hi there

This approach may give you some ideas:

Produce a colour/condition-code: EG
A1:A10 format each cell a different colour.
Type 10 in A1, 20 in A2 etc up to 100 in A10.

Border the conditional format range D1:F10.

Border C12. The number in this cell activates the conditional formatting via event macro/s.

Right click your sheet tab, left click View Code and paste in the following code:

Private Sub Worksheet_Calculate() 'IF VALUE IN C12 IS RESULT OF FORMULA
On Error GoTo Errorhandler
Dim x As Byte
Dim y As Range
x = WorksheetFunction.Match(Range("C12"), Range("A1:A10"), 0) - 1
Set y = Range("A1").Offset(x, 0)
Range("D1:F10").FormatConditions.Delete
Range("D1:F10").FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MATCH($C$12,$A$1:$A$10,0)>=1"
Range("D1:F10").FormatConditions(1).Interior.ColorIndex = y.Interior.ColorIndex
Exit sub
Errorhandler:
Range("D1:F10").FormatConditions.Delete
Msgbox "C12 value is out of A1:A10 colour range"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range) 'IF SELECTION CHANGE TO C12
On Error GoTo Errorhandler
If Target.Address = "$C$12" Then
Dim x As Byte
Dim y As Range
x = WorksheetFunction.Match(Range("F13"), Range("A1:A10"), 0) - 1
Set y = Range("A1").Offset(x, 0)
Range("D1:F10").FormatConditions.Delete
Range("D1:F10").FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MATCH($C$12,$A$1:$A$10,0)>=1"
Range("D1:F10").FormatConditions(1).Interior.ColorIndex = y.Interior.ColorIndex
End If
Exit sub
Errorhandler:
Range("D1:F10").FormatConditions.Delete
Msgbox "C12 value is out of A1:A10 colour range2"
End Sub

If the number in C12 equals a number in your colour/condition-code then your conditional format range will change to that colour.

There are 2 event macros - one operates if C12 contains a formula and the other if its value is changed directly.

Modify to your needs
regards
Derek
This message was edited by Derek on 2002-11-06 07:00