VBA to Highlight Cells that Exceed the Value in Another Cell

I'd appreciate some assistance with the following:

Range K7:AH43 contains a mix of cells with numbers and cells with text. I'm firstly trying to sum the highest numbers in the range to achieve a threshold total value, which I do in cell F6 with

=SUMIF(K7:AH43,">"&F5)

I put different numbers in F5 until I achieve the total value that I'm after. No problem up to that point.

However, once I establish the value in F5 that gives me the desired total, I then want to highlight all the numerical cells in range K7:AH43 that are above the value in F5. How do I achieve it? I've tried conditional formatting but it highlights text as well as numbers. I'd like to use VBA event coding that highlights the cells whenever F5 is updated - or by whatever other method achieves the result. Colourwise, I'm using the standard colour yellow.

Many thanks.

Hi Alan

It's giving a 'Type Mismatch' error pop-up.

Debug highlights the line:

Set crit = Range("F5").Value

so I went back to the previous line and tried various changes. By deleting ".Value", so that it became just:

Set crit = Range("F5")

it then correctly highlighted the cells in range K7:AH43 that were above the value in F5, but it also highlighted all the cells that contain text. This is the same problem that conditional formatting was giving me.

With 16 as the value in cell F5, here's part of the K7:AH43 range:

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"F5_Val.png","data-attachmentid":1210584}[/ATTACH]

The cells containing text are already formatted as text, as a result of the earlier conditional formatting problem.

Before posting this plea for help I'd noticed from extensive browsing, references to SpecialCells and xlNumbers. Does that somehow need to be added in, to ensure that only the cells containing numerical values are highlighted?

Thanks.

Hi Alan

Don't worry - I've solved it. After more searching I discovered a thing called IsNumeric, so what I now have is:
Option Explicit

Sub HigherThanF5()
    Dim crit As Variant
    Set crit = Range("F5")
    Dim c As Range, rng As Range
    Set rng = Range("K7:AH43")
    rng.Interior.ColorIndex = xlNone
    For Each c In rng
        If IsNumeric(c.Value) Then
            If c > crit Then
            c.Interior.ColorIndex = 4
            End If
        End If
    Next c
End Sub

I had to add in the line:

rng.Interior.ColorIndex = xlNone

because I found that when I changed the value in F5 it was leaving cells in K7:AH43 wrongly highlighted.

So now - all good. Thank you so much for your guidance. Between that, and my determination to try to work it out for myself, we've achieved the desired result. Another satisfied customer!