Conditional formatting failing in one cell when numbers treated as text

andrewmrichards

New Member
Joined
Aug 28, 2013
Messages
7
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:

Excel conditional formatting issue.png


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

(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).

VBA Code:
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:

VBA Code:
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,238
Office Version
  1. 365
Platform
  1. Windows
It's because in text terms 9 > 4, try
VBA Code:
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

New Member
Joined
Aug 28, 2013
Messages
7
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,238
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
With ws.Range(rng.offset(3, iCol), rng.offset(LastRowNum - 5, iCol))
   .NumberFormat = "general"
   .Value = .Value
End With
 

andrewmrichards

New Member
Joined
Aug 28, 2013
Messages
7

ADVERTISEMENT

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
VBA Code:
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

New Member
Joined
Aug 28, 2013
Messages
7
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
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,238
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,302
Members
414,375
Latest member
Onmyown

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top