Best method of changing text color?

ScotTFO

Board Regular
Joined
May 30, 2008
Messages
72
I have a document and the cells contents are being generated by VBA code and they look simular to this.

21.00 MB
43.21 KB
123.4 GB
-323.2 MB

Etc etc. It's all file sizes though some can be negative.

What I am trying to do is to change that cells font color to red if it's negative and green if it's posotive. I am not too sure how to do this with VBA but I was also wondering if this is possible to do with Conditional Formatting?

I am aware of using LEFT and RIGHT to seperate the values when using IF, but I am not sure how this would be utilized in conditional formatting or if there's an easier VBA solution.

Thanks!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Try using conditional formatting:

Formula is: =left(A1,1)="-"

Formula is: =left(A1,1)<>"-"

Then format the first one as red and the 2nd one as green.

Hope that helps.
 
Upvote 0
=LEFT(A1,1)="-"

That will return TRUE or FALSE which is what condtional formatting needs...
so if it's true, it means it's a Negative Number, if it's False, it's a Positive Number.

Hope This Helps
 
Upvote 0
Are these entered as text strings, or as numbers ?

If they are numbers, you can use basic formating.
Set the default font colour to green, and then use a custom format such as
#,##0;[Red]-#,##0

If they are text or numbers, you can also use conditional formating.
Again, set the default format to green.
If you have text,
Conditional Formating, Formula Is, =LEFT(A1,1)="-", and format as red font.
If you have numbers,
Conditional Formating, Cell Value Is, Less Than, 0 and format as red font.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top