Format Custom number's color

[sungen99]

I see how i can change the color using the words "Green", "Red" and so on. What i REALLY need to do is make the color, #C00000.

Anyway to do this????

example.jpg

[jeffreybrown]

In custom format you can use the Red, Green, Black, etc, but you can also use Color 1, Color 2, Color 3 … Color 56.

Would one of those colors help?

[sungen99]

What number would be the red that i circled?

Attachment 664878

[jeffreybrown]

Not sure, but this might help you key in on the color you want.

https://chandoo.org/wp/custom-number-formats-colors/

Look under Color Number

[sungen99]

BAH, great info thank you but it does not help me... i type in "Dark Red" and it rejects it.

[jeffreybrown]

Yes, I see what you mean. The article says you can type the color name in, but it also says thru 2013. I just tried in 2016 and it will not take a typed in color, and since you are using 365, maybe not there either.

This will work though. [Color 30]$#,##0.00

Outside of this, the custom format, if you want to apply a specific color you would have to use VBA.

[sungen99]

VBA it is i guess.... but sadly dont know how.

[jeffreybrown]

Without knowing what, when, and how you need this applied, it's hard to suggest a method. Before jumping into VBA, we need to see your layout and a before and after. Will one of the 56 colors not work for you? VBA opens up a whole new world.

One example, if this is a workbook you will be sharing with others, the only way the VBA will work is if they enable macros when opening the workbook. If they don't, the VBA is not of much use.

What about using conditional formatting? You can pick all sorts of colors and based on your conditions. Let's say, value is greater than 0, Dark Red. Value is less than 0, Dark Green. Can you use conditional formatting?

I'm not trying to talk you out of VBA, just exploring whether there are other options. If Dark Red is not one of the 56 colors, is there something close.

[sungen99]

I already did the conditional formatting option. But as you can see below I am required to have two columns to accomplish this task. I was just looking for a cleaner solution.

Attachment 664892

[jeffreybrown]

I don't know what a "cleaner solution" could be! For me, conditional formatting is clean and would happen on change of the spreadsheet. Understanding the picture you posted is column A:D with column C the column looking for negative/positive

Conditional Formatting

• Highlight applicable range >> =$A$2:$D$14
• Home Tab >> Styles >> Conditional Formatting
• New Rule >> Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =$C2<0
• Format… [Number, Font, Border, Fill]
• OK >> OK

Now do this again for a positive number. It's hard to work with a picture, so if you have further troubles, please post a workbook with a clear before and after. Instructions at top in "yellow" banner.