More conditional formatting please?

Duritz

Board Regular
Joined
Apr 28, 2005
Messages
226
Anyone know if there's any way to get more than three conditional formatting applied to a given cell?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Not natively.

You need to use VBA, but it's easy enough.

Care to expand on what you're trying to do?

Smitty
 
Upvote 0
Not through the Conditional Formatting menu item, but you can do it via VBA code (using Worksheet/Workbook events). What is it that you want to do? What formats do you wish to apply and under what circumstances?

Best regards

Richard
 
Upvote 0
Plus 1 = 4 conditions if you count the regular format of the cell. If the contents don't meet any of the "Conditional Formats" cases, it results in the regular format of the cell being displayed. If you count negative and postive as separate conditions in the regular format, I guess that's 5 conditions in all.

Dufus
 
Upvote 0
Thanks guys. What I need is for a given row to have cell shading applied if the figure in column A is the highest in the set. Example - rows 1-100 are being considered, if the figure in column A is the highest for rows 1-100, cell shading applied.

Additionally, if the figure in column A = 1 then cell shading would need to be applied.

There are already three conditional formats applied to these cells (and Dufus yeah I use your method for an effective fourth also), so if anyone could show me some VBA code it'd be greatly appreciated.
 
Upvote 0
Duritz, can you list out the entirety of the conditions you want to apply - you may as well do the whole lot via VBA rather than just those in excess of the 3 allowed.

Thanks

Richard
 
Upvote 0
No worries Richard. Here's the info.

Range in question is R7:BD150

For each row in that range,

if cell BF (& row number) = 0, no format.
if cell BF (& row number) = 1, text colour = green
if cell BF (& row number) = 2, text colour = blue
if cell BF (& row number) >=3, text colour = brown
if cell W (& row number) = 1, cell shading = yellow
if cell BC (& row number) is the highest in the set BC7:BC150, cell shading = yellow.

Hope that makes sense,

Cheers!
 
Upvote 0
Duritz

Try the following code - needs to go into the sheet code module of the relevant worksheet (right-click on the sheet tab in Excel and select View Code and paste it in there):

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = True
Dim chkRng As Range, chkRng2 As Range, i As Long
Set chkRng = Range("BF7:BF150,W7:W150")
Set chkRng2 = Range("BC7:BC150")

If Not Intersect(Target, chkRng2) Is Nothing Then
    For i = 7 To 150 Step 1
        If Cells(i, "BC").Value = Application.WorksheetFunction.Max(chkRng2) Then
            Range("R" & i & ":BD" & i).Interior.colorindex = 6
        Else
            Range("R" & i & ":BD" & i).Interior.colorindex = 0
        End If
    Next i
End If

If Not Intersect(Target, chkRng) Is Nothing Then
    Select Case Cells(Target.Row, "BF").Value
            Case 1
                Range(Cells(Target.Row, "R"), Cells(Target.Row, "BD")).Font.colorindex = 4
            Case 2
                Range(Cells(Target.Row, "R"), Cells(Target.Row, "BD")).Font.colorindex = 8
            Case Is >= 3
                Range(Cells(Target.Row, "R"), Cells(Target.Row, "BD")).Font.colorindex = 9
            Case Else
                With Range(Cells(Target.Row, "R"), Cells(Target.Row, "BD"))
                     .Font.colorindex = xlAutomatic
                 End With
    End Select
    If Cells(Target.Row, "W").Value = 1 Then _
        Range(Cells(Target.Row, "R"), Cells(Target.Row, "BD")).Interior.colorindex = 6
End If
End Sub

Note that if the values in BF and W and BC are derived via formula (rather than being typed in) you will likely need to use an alternative event and making a couple of adjustments to the code - post back if this is the case.

Hope this helps!

Richard
 
Upvote 0
Richard thanks for that - won't paste it in because as you suggested the values are derived using formulas. The value in W is printed in from an Access query so will be fine but the ones in BC and BF are formulas.

Could you inform this VBA learner what changes to that code would be necessary?
 
Upvote 0
Oh yeah and here's another little query for you: It does it for that range 7:150, and then needs to repeat it another 23 times for for 207:350, 407:550 etc. So, some sort of lnecnt where after each time it adds 200 to the lnecnt?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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