using vba for conditional formatting

[QuizToon]

Hi all,

I have a table with 12 columns. I would like to highlight the highest and lowest entry in each column. Highest in green and the lowest in red.

there is a lot of copying and pasting which I find messes up the standard method so was wondering if VBA would work better

Many Thanks

 

[lrobbo314]

Strange, I made a test workbook and it seems to work well. Maybe posting your data would help. I don't know why it wouldn't work. The only thing I can think of off the top of my head is that your numbers are text maybe... Did Fluff's solution not work as well?

 

[Fluff]

Are you values actual numbers, or text that look like numbers?
In a blank cell use
=ISNUMBER(C6)
What is the result?

Also with my code from post#8 did you get any error messages?
Also what sort of values do you have?

 

[Fluff]

One other thing, with the code from lrobbo314 make the 2 changes shown here

Dim Low As [COLOR=#ff0000]Double[/COLOR]
Dim High As [COLOR=#ff0000]Double[/COLOR]

Does that help?

 

[My Aswer Is This]

When I try your code it works for me. But I'm never sure about using CurrentRegion
Not exactly sure how that works if there are empty cells in the region.

That's why I would define my range using Table as the range. And Table ranges expand.


QUOTE=lrobbo314;5155499]Strange, I made a test workbook and it seems to work well. Maybe posting your data would help. I don't know why it wouldn't work. The only thing I can think of off the top of my head is that your numbers are text maybe... Did Fluff's solution not work as well?[/QUOTE]

 

[lrobbo314]

One other thing, with the code from lrobbo314 make the 2 changes shown here

Dim Low As [COLOR=#ff0000]Double[/COLOR]
Dim High As [COLOR=#ff0000]Double[/COLOR]

Does that help?

Good call. I used RandBetween for my test data. Didn't think that they might not be whole numbers. Might be the pessimist in me, but I have a feeling it won't be the answer.

 

[lrobbo314]

When I try your code it works for me. But I'm never sure about using CurrentRegion
Not exactly sure how that works if there are empty cells in the region.

That's why I would define my range using Table as the range. And Table ranges expand.

I agree. Not a big fan of CurrentRegion. But, I mentioned that in my first post. It's a nice little shortcut, especially in a situation like this where I wasn't really sure where their data was.

But, yes, make everything a table! So much easier and unambiguous.

 

[Fluff]

Might be the pessimist in me, but I have a feeling it won't be the answer.

Unfortunately I suspect that you are right.
My best guess is that the data is text.
But, fingers crossed, we might both be wrong.