conditional formatting question

[fujimi-cho]

Hi,
I have data supplied to me as: 36/40. In this example, 36 is a person's average score & 40 is the most recent score. In a column of such figures, is it possible to have conditional formatting highlight just the highest recent score? I realize the simplest solution is to split these figures into 2 columns, but would rather rather keep them intact if possible.

Fujimi-cho

[VBA Noob]

Don't think you can with CF.

Here's a macro that bolds the last 2 digits if greater than the first 2 for each cell in your selection

Link on where to add code

Sub xxx()
Dim Cel As Range
For Each Cel In Selection
If Right(Cel, 2) > Left(Cel, 2) Then
With Cel.Characters(Start:=4, Length:=5).Font
       .FontStyle = "Bold"
End With
End If
Next Cel
End Sub

VBA Noob

[WinteE]

Hi Fujimi-cho,

This can't be done by just using Excel's Conditional Format option. But with VBA it is possible as you can see in the attached file.

Select the range with scores and click the button.

Succes,
Erik

[VBA Noob]

I assume you don't want to press a button so try this event macro

You can either open the attached example or right click your sheet tab > select view code and paste in the below. It works on Col A and makes it bold and red ColorIndex 3.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Right(Target, 2) > Left(Target, 2) Then
With Target.Characters(Start:=4, Length:=5).Font
       .FontStyle = "Bold"
       .ColorIndex = 3
End With
Else
With Target.Font
       .FontStyle = False
       .ColorIndex = xlAutomatic
End With
End If
End If

End Sub

VBA Noob

[WinteE]

You don't take into account the possibility that the scores are < 10 and/or > 99 VBA Noob.

[VBA Noob]

Thanks WinteE,

Now amended

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Dim i As Integer
On Error GoTo trap:
i = WorksheetFunction.Find("/", Target)
If Val(Right(Target, Len(Target) - i)) _
> Val(Left(Target, i - 1)) Then
With Target.Characters(Start:=i + 1, Length:=999).Font
       .FontStyle = "Bold"
       .ColorIndex = 3
End With
Else
With Target.Font
       .FontStyle = False
       .ColorIndex = xlAutomatic
End With
End If
End If
trap:
End Sub

VBA Noob

[fujimi-cho]

Thanks to VBA Noob & winteE; y'all confirmed my doubts about conditional formatting doing the job. I'll give your suggestions a try!

Fujimi-cho