Conditional formatting failing in one cell when numbers treated as text

[andrewmrichards]

Hi all

I have a SQL back end database, with an Access front end. One of the many things the Access application does is create a report in Excel by taking a SQL view, and exporting it, then doing lots of formatting and generally improving its appearance.

The database is designed to record student marks in a series of exams. The column containing the mark ususally contains a number, but may contain ABS (for students who were absent) or a number followed by (F) if part of the exam was failed but there's still a mark overall. Therefore, the database exports this column as text (VARCHAR) rather than a number.

This hasn't caused a problem, until now...

As part of the formatting work, the VBA applies conditional formatting to the cells, highlighting
A) any cell containing ABS, and
B) any cell which isn't blank, but contains a value less than the pass mark (which is shown at the top of the column).

This has always seemed to work well, but we've noticed one cell, amongst the many reports, where it's not working:

Here's the conditional formatting in use in this cell (and all other cells in the column):

(There's a second rule which handles the display of ABS)

In this sheet, H is the column containing the "problematic" value, H7 contains the pass mark, and the data values begin in H9 (the rule is applied for the whole range, thus the use of mixed references).

If I overtype the original value of 9.2 with 9.2 (again), it conditionally formats as expected - and right-aligns, which is why I suspect that this is to do with it being treated as text rather than number.

In case it helps, here's the code that outputs the data originally from the database (cmd is an ADO command object, rs an ADO recordset object, xlRng an Excel Range object).

cmd.CommandText = "EXEC Results.spExamBoardPivotData @ExamYear = '2020',@StudyYear = 'Year 1", @DegreeProgCode='A101'")

Set rs = cmd.Execute

xlRng.copyfromrecordset rs

and here's the code that adds the conditional formatting (sorry - lots of variables, mostly used for looping through various columns), but hopefully you can pick out what I'm doing:

With ws.Range(rng.offset(3, iCol), rng.offset(LastRowNum - 5, iCol)).FormatConditions _
     Add(Type:=xlExpression, Formula1:="=AND(" & rng.offset(3, iCol).Address(False, True) & "<>" & Chr(34) & Chr(34) & "," & rng.offset(3, iCol).Address(False, True) & "<" & rng.offset(2, iCol).Address(True, True))
         With .interior
              .Color = RGB(255, 204, 255)
        End With
        .Font.Color = RGB(255, 0, 0)
End With

So, two questions:
1. Any clue why this one value might not be working when it works for all other values, even though they all output as text?
2. What might be the best way (programmatically) of telling Excel to treat these values as numeric when they are numbers, but not when they're not?

Thanks for your help!
Andrew

 

[Fluff]

It's because in text terms 9 > 4, try

Add(Type:=xlExpression, Formula1:="=AND(" & rng.offset(3, iCol).Address(False, True) & "<>" & Chr(34) & Chr(34) & ",value(" & rng.offset(3, iCol).Address(False, True) & ")<value(" & rng.offset(2, iCol).Address(True, True)&")")

 

[andrewmrichards]

Perfect - of course it is...

Thanks for that - worked like a charm.

Do you have any thoughts as to my second question? The client has asked "Why are the numbers being treated as text"? I can explain why, of course, but really what they're wanting is that the numbers are treated like numbers - right-aligned, numeric values. They've said it's not the end of the world to have them treated as text, but, well, y'know....

 

[Fluff]

I've no idea why there are being imported as text & the best option would be to figure that out & import them as numbers.
That said you could try

With ws.Range(rng.offset(3, iCol), rng.offset(LastRowNum - 5, iCol))
   .NumberFormat = "general"
   .Value = .Value
End With

 

[andrewmrichards]

I've no idea why there are being imported as text & the best option would be to figure that out & import them as numbers.
That said you could try

With ws.Range(rng.offset(3, iCol), rng.offset(LastRowNum - 5, iCol))
   .NumberFormat = "general"
   .Value = .Value
End With

Thanks - I'll take a look at using the NumberFormat property. Sorry - should've been clearer. I know why they're being imported as text (as I said in my original post, the column needs to be VARCHAR as it's sometimes got things like ABS in it. My question was really about how I can, given that I have to export the column as text, get Excel to treat the numeric values in the column as numbers.

I'll try the NumberFormat property.

Thanks again.
Andrew

 

[andrewmrichards]

I've no idea why there are being imported as text & the best option would be to figure that out & import them as numbers.
That said you could try

With ws.Range(rng.offset(3, iCol), rng.offset(LastRowNum - 5, iCol))
   .NumberFormat = "general"
   .Value = .Value
End With

Yep - again, implemented your suggestion and it was just the job.

Thanks again for your help - much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback.