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


    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:


    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?


  • 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

    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!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!