SEARCH

The How-To Geek Forums Have Migrated to Discourse

How-To Geek Forums / Microsoft Office

(Solved) - Excel Conditional Formatting

(7 posts)
  • Started 1 year ago by davekenyon
  • Latest reply from moreeg
  • Topic Viewed 840 times

davekenyon
Posts: 4

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

Posted 1 year ago
Top
 
moreeg
Posts: 842

Hi Dave

It sounds like a number formatting problem. When you "split" the data into individual cells the "numbers" you are trying to conditionally format are actually text and therefore cannot be recognised by the conditions of the format. One way to test this is after you've run the macro rather than overtyping one of the values simply double click on one of them and press enter. This will often change text to numbers in cases like this.

If I am right the resolution to this would be to alter how the Macro works and to do that we would have to see the Macro and get a sample input file.

Let us know how yo'd like to proceed.

Posted 1 year ago
Top
 
davekenyon
Posts: 4

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

Posted 1 year ago
Top
 
davekenyon
Posts: 4

Having read through it, I think the 'On Error Resume Next' is a hangover from a previous version!

Posted 1 year ago
Top
 
moreeg
Posts: 842

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

there may be something we can do with how you copy the data. For example, if your main sheet "copies" the imported values from the background sheet as a calculation e.g.

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.

Posted 1 year ago
Top
 
davekenyon
Posts: 4

Hi Moreeg

I don't use all the data that the function downloads, and I also perform some calculations on it, so I use the function to download the data onto one sheet and then extract what I need from there.

Result!! The Value command has fixed it!! Thanks very much for your time - I was just going around in circles!

Posted 1 year ago
Top
 
moreeg
Posts: 842

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

Posted 1 year ago
Top
 



Topic Closed

This topic has been closed to new replies.