How-To Geek

Hi

I must be getting something wrong!

I'm trying to set up a simple conditional format, such that a cell is green if its value is higher than another cell. I can get this to work if I type numbers into the 2 cells; it fails when I try the same thing on my data in situ!

The data I'm trying to use it on is copied from a 'background' sheet which, using a small VBA module, gets the data from a .csv file. The VBA uses the SPLIT function to separate the data into individual cells onto the background sheet.

I mention this because I'm suspicious that this may be causing the conditional formatting problem. If I type over the data which is subject to the conditional formatting with the numbers they display, it all works as I'd hoped!

Could somebody please tell me where I'm going wrong?!

Thanks

Hi Moreeg

Thanks for the quick reply. If I double click on the cell it comes up with the formula and then pressing enter returns it to the original figures.

This is the VBA module I've been working on. The comments are for me - so that I can remember how it works with an array! I'm new to this and I've copied bits of it from elsewhere so, if you could also explain the 3 lines after set.http I'd be very grateful! The function needs to be called with a stockmarket ticker (eg goog or msft) and I then enter the formula into an array of 21 columns.

Function DK(ticker As String)

Application.Volatile

'Needs to be called as an array function
'Select the required number of columns in the target row
'call the DK function in the formula bar and hold down SHIFT+CTRL as pressing ENTER

Dim url As String
Dim http As Object
Dim csv As String
Dim temp() As String

'downloads csv file with ticker, name (n) and last price (l1)

url = "http://download.finance.yahoo.com/d/quotes.csv?s=" & ticker & "&f=sna2c1hgm3l1opm4vkjabf6j1s1j2t7&ignore=.csv"

On Error Resume Next

Set http = CreateObject("MSXML2.XMLHTTP")

http.Open "GET", url, False

http.Send

csv = http.responseText

temp = Split(csv, ",")

temp(0) = Mid(temp(0), 2, Len(temp(0)) - 2)
temp(1) = Mid(temp(1), 2, Len(temp(1)) - 2)

DK = temp

Set http = Nothing
End Function

Hi Dave
Sorry it took so long to respond. Well ... I was right in my diagnosis - the conditional formats won't work because the data you are importing is being imported as "Strings" instead of numbers (defined in the last 2 DIM statements). But I never imagined HOW you were importing the data. It is quite ingenious.

Can you explain

The data I'm trying to use it on is copied from a 'background' sheet

A1 = Sheet2!A1

where Sheet2!A1 contains your imported data then you would just need to change that to A1=Value(Sheet2!A1)

EDIT: the "resume on error" is not a problem.

Well - sometimes a shot in the dark finds its mark - with a good deal of luck. Glad I could help.