[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-12-20
9
Medium Priority
?
362 Views
Last Modified: 2012-05-08
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!
0
Comment
Question by:Number5ix
  • 5
  • 3
9 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26092631
Will the data always end in a number followed by two letters?
0
 
LVL 3

Author Comment

by:Number5ix
ID: 26093048
Yes it will always end with "GB" (no quotes obviously) but the numbers can be anything - they range from 75 to 1126 currently.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26094492
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.

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 3

Author Comment

by:Number5ix
ID: 26094509
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
0
 
LVL 12

Expert Comment

by:sdwalker
ID: 26097826
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
0
 
LVL 3

Author Comment

by:Number5ix
ID: 26120551
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!
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1500 total points
ID: 26144552
You cannot use data bar CF without using a cell that contains an actual value I'm afraid. You could do standard pattern fills though?
0
 
LVL 3

Author Comment

by:Number5ix
ID: 26151529
rorva: Ok cool, that's very helpful, thanks!  Is there a way to do standard pattern fills in VBA?
0
 
LVL 3

Author Closing Comment

by:Number5ix
ID: 31668328
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.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question