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
– Matteo NNZ, Commented Feb 21, 2015 at 12:20
...and you can also resort to =VALUE(...) in a conditional format rule. — KekuSemau
– KekuSemau, Commented Feb 21, 2015 at 12:26
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
– user2482756, Commented Feb 21, 2015 at 12:53

user4039065 · Accepted Answer · 2015-02-21 15:43:48Z

1

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.

answered Feb 21, 2015 at 15:43

user4039065

Sign up to request clarification or add additional context in comments.

7 Comments

user2482756 Over a year ago

Thank you Jeeped , sometimes the "JS_topStoreCount" element doesn't exist on the page so the formula return as #VALUE! how can I return as "0" instead?

user4039065 Over a year ago

@user2482756 - That comment seems to contradict your earlier statement that "the return will ALWAYS be integer". It seems a fool's errand to keep trying to compensate for conditions that you are unwilling to disclose.

user4039065 Over a year ago

@user2482756 - How about =IFERROR(giveMeValue(A1), 0) ?

user2482756 Over a year ago

no doesn't seems to be working , I found the work around for the "0" matter but now still have the previous problem with the color formatting , I updated the post with the new code

user2482756 Over a year ago

I solve it with macro as follow :

'Dim color As Integer   For Each cell In Sheets(1).Range("B1:F65536")      If IsEmpty(cell) Then GoTo nextcell:     If Not IsNumeric(cell.Value) Then GoTo nextcell:      If cell.Value > 14 Then       color = 4     ElseIf cell.Value < 8 Then color = 3     Else: color = 6     End If     cell.Interior.ColorIndex = color nextcell:   Next cell  End Sub'

|

Collectives™ on Stack Overflow

Conditional Formatting through code

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related