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?
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.
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.
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.
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):
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
Range("R" & i & ":BD" & i).Interior.colorindex = 0
If Not Intersect(Target, chkRng) Is Nothing Then
Select Case Cells(Target.Row, "BF").Value
Range(Cells(Target.Row, "R"), Cells(Target.Row, "BD")).Font.colorindex = 4
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
With Range(Cells(Target.Row, "R"), Cells(Target.Row, "BD"))
.Font.colorindex = xlAutomatic
If Cells(Target.Row, "W").Value = 1 Then _
Range(Cells(Target.Row, "R"), Cells(Target.Row, "BD")).Interior.colorindex = 6
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.
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?
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?