formating cells using formula rather than menus/VBA

  • Thread starter Thread starter Andreww
  • Start date Start date
A

Andreww

Hi - Is this possible. I just want to colour a cell with blue blocks
without having to go and use format cells from menu.

Furthermore can I conditionally format (without going to the menu). For
instance can I say if value of cell A2 is 23 then colour it red else
blue etc.

Thanks

Andrew
 
Hi Andrew,

Yes, you could do that, via an event-driven macro, but why isn't
conditionally formatting the cells sufficient?

Cheers
 
You could use VBA, and change-event macros. But what do you find
objectionable about Conditional Formatting?.....it would seem the natural way
to do this.

Vaya con Dios,
Chuck, CABGx3
 
Thanks but I probably didn't explain this too well...

While analysing data I frequently output "group by"'s or frequency
counts - eg age band and count in each band.

Copy this (from sql environment) into xl. Now I want to show column
%'s to see which age bands are important to my analysis.

Then so that I can graphically see which bands are important I put a
formula to the right of the col %'d which says =rept(char(12),b2) which
when copied down gives me a simple bar chart. Just to make it look a
bit better, rather than have a series of blocks I'd like more like a
line/bar. If I coloured the character in there to be of the right size
and colour, I think that would give me what I want.

If I just have the one profile, then yes I could do conditional formats
(though I'm not sure it would work as I want) or I could poss use some
VB. Using VB in an XLS which then goes to someone else is isn't
particulary PC savvy always causes problems.

But... all I want is something in the formula to say
=rept(format(char(12),"redbox"),b2) or something so that I can simply
use as a generic across all my profiles. I know format() doesn't exist.

Does this make it any clearer... or have I really muddied it up now!?

Thanks

Andrew
 
Here are some alternative symbols you could use which look a bit better
than CHAR(12) in Arial:

CHAR(7) - filled circle
CHAR(8) - filled rectangle with hollow circle
CHAR(17) - filled triangle
CHAR(129) - slimmer rectangle than char(12)

You only need to set the colour of the original formula to red once
(when you type it in), then copy down - all of the "bar charts" will
appear red.

Hope this helps.

Pete
 
Looking at your first post again, you do not need to use format cells
to get red (or blue) - just click the font colour icon.

Pete
 
Maybe you could just use a hyphen, or underscore, instead of the Char(12)
symbol and format it for BOLD-RED.........

Vaya con Dios,
Chuck, CABGx3
 
Back
Top