Conditional Formatting through code

Question

Hello I am parsing some data from other website using this Request :

   Public Function giveMeValue(ByVal link As Variant) As Variant

Set htm = CreateObject("htmlFile")

With CreateObject("msxml2.xmlhttp")
   .Open "GET", link, False
   .send
   htm.body.innerhtml = .responsetext
End With

If Not htm.getelementbyId("JS_topStoreCount") Is Nothing Then

   giveMeValue = htm.getelementbyId("JS_topStoreCount").innerText

Else
    giveMeValue = "0"

End If

htm.Close
Set htm = Nothing

End Function

with the function on the cell =giveMeValue(A1) now I need to apply some formatting condition to copied value but I am unable to do it with the excel conditional formatting

The active cell "B" column

Less than 10 Red
Between 10 and 15 Yellow
More than 15 Green

Aall the cell not returning any number should be blank , please consider the =giveMeValue(A1) return as string

I recognize this code snippet :) Have you tried to declare the function As Integer rather than As String, if you know that the return would always be convertible to an Integer? I guess the problem is that conditional formatting doesn't recognize the number because it's returned as a string, as you correctly say. — Matteo NNZ
...and you can also resort to =VALUE(...) in a conditional format rule. — KekuSemau
Matteo of course you recognize it :-) is your and all credits goes to you of course , I am not able to do it by myself , the return will always be integer — user2482756

Unknown Unknown · Accepted Answer · 2015-02-21T15:43:48

Integers (and Long integers) cannot by nature contain fractional (aka decimal) portions of a number. I strongly suspect that this is an important factor but any details that would clear this up are missing from your question.

Remove the As String in the function declaration. This effectively changes it to the default of As Variant. The only other operation would be to change one code line to,

giveMeValue = CDbl(.innerText)

This should return a numerical value whether there is a decimal involved or not. The function will return an error if text that cannot be converted to a number is retrieved from the scrape.

Conditional Formatting through code

1 Answers