VBA Cond. Format mod

Gareth Brown

Board Regular
Joined
Jul 29, 2002
Messages
52
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

kieran

Active Member
Joined
Oct 27, 2002
Messages
429
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

Board Regular
Joined
Jul 29, 2002
Messages
52
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

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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
 

Forum statistics

Threads
1,144,510
Messages
5,724,778
Members
422,577
Latest member
madsifonvdijf

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
Top