asked on

Can I use VBA or functions to apply conditional formatting in Excel?

Hi all,

I've got a whole bunch of Excel VBA functions that work really well and produce some data re disk space usage. The results are in TEXT format, for example:

500GB vraid1 (1000GB used), savings = 375GB

This means that conditional formatting won't work seeing as the cell's contents aren't a number.

Is there a way I can apply conditional formatting from within VBA? I know I can format cells as numbers etc but that's not what I'm after.

I'm specifically wanting to apply a data bar conditional format if the number at the END of the example above is a certain number (i.e. 375 would produce a blue bar).

Thanks!

Rory Archibald

Will the data always end in a number followed by two letters?

Number5ix

ASKER

Yes it will always end with "GB" (no quotes obviously) but the numbers can be anything - they range from 75 to 1126 currently.

Rory Archibald

Would it be OK to use a helper column? If so you can extract the number using a formula like:
=-LOOKUP(2,-RIGHT(LEFT(A1,LEN(A1)-2),ROW(INDIRECT("1:"&LEN(A1)-2))))
and then apply the databar CF based on that numeric cell.

Number5ix

ASKER

Thanks for the answer but unfortunately, no. :( I used to have helper columns for that sort of thing but the worksheet ended up with too many columns - I'm trying to minimise the number of visible and hiding them isn't an option either, unfortunately.

I was hoping for a VBA function called "ConditionalFormat(format, cell)" or something like that hehe

sdwalker

You could format the cell with the text of each drive. You'll probably need to see the attached screenshot to see what I mean. Then you just enter the value in the cell and you can do whatever conditional formatting you like.
If you need to set the format in code, you would need to do something like the code below.
Good luck,
sdwalker

Range("A1").NumberFormat = """500GB vraid1 (1000GB used), savings = """ & 0 & """ GB"""

Open in new window

cellformat.bmp

Number5ix

ASKER

Hi sdwalker,

That's helped me solved another issue I was having but doesn't quite solve this one as it's visual/conditional formatting I'm looking for, sorry. Thank you though!

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Number5ix

ASKER

rorva: Ok cool, that's very helpful, thanks! Is there a way to do standard pattern fills in VBA?

Number5ix

ASKER

Partially complete as I received no response about how to do pattern fills in VBA. Good answer though, confirmed that I can't use data bars without a cell that contains an actual value.