Multiple colors in one cell

[Endlessfacepalms]

Hi excel experts :)

I'am using excel to calculate in-game currency income and expences.
The currencies are:
100 Copper, makes 1 Silver
100 Silver, makes 1 Gold

Currently I'am using this Userdefined formula code to make excel automatically sort the currencyes:

Please Login or Register  to view this content.

I would like to make it, that the value belonging to "G" is in yellow. "S" in Grey, and "C" in Brown.
ex. 1624G 26S 15C

Alongside with the above mentioned formula I am using a macro. But instead of changing the letters its commanded to, it is changing the color for all the letters.

Please Login or Register  to view this content.

Is it possible to add this funktion to the formula, or do I have to make it using VBA?
-can you help me achieve this?
I am a beginner to excel, and I found the above mentioned formula in Reddit.

Kind regards,

[Keebellah]

Hi, welcome to the forum.
I would suggest you apply conditional formatting for the cell coloring based upon the cell value.
You can add multiple conditions

[Endlessfacepalms]

Hi Hans, and thank you for the answer.
I have ben fiddiling arround with that until I hit the wall. and not being able to move on.
Can you show me how I can use the method you mentioned in your previous reply?.

Kind regards,

[Keebellah]

Sample file?

[Endlessfacepalms]

Here it is.

[Keebellah]

Okay, I'll take a look but i found some links (Google)
http://blog.contextures.com/archives...rrency-symbol/

https://www.youtube.com/watch?v=NzwQ_BbD2G4

[Keebellah]

I have the file but do not see any values in G that make sense to me.
I suggest you add some info like actual and what you want to see.

[Endlessfacepalms]

Thank you for the video guide
I took a look at the website. He doesnt show how to make a currency that have 000,00,00 and giving each dicimal its own color.
I look forward to hear from you again.

EDIT:

G: Gold
S: Silver
C: Copper

1G is 100S or 1G is 1000C
1S is 100C

[Keebellah]

Then I do not understand what you mean

Please Add a manual result of what you want to see as result to the sample file.
It seems that I do not understand your question.

[Endlessfacepalms]

I will try my best.

In a cell I would like to type the numbers 54836 without any commas or dots. The cell should convert the numbers automatically to the correct currency format and add the currency symbol for each part, which will be: 5G 48S 36C
another example is, if I type 6872469. The cell should converts these numbers to the correct currency and add the currency symbols for each part, which will be: 687G 24S 69C
and here is the same example but with a larger number 65873541. Will be converted to 6587G 35S 41C
the numbers 103879454 should be converted to 10387G 94S 54C

[Endlessfacepalms]

To round all up. I would like to type a number in a cell. Lets use 23456789 as an example.
In a cel I type: 23456789
Cell auto formats the numbers to: 2345 67 89
and gives each section of the numbers its own currency letter: 2345G 67S 89C
and gives each section its own color: 2345G 67S 89C

[Keebellah]

I think it's going to be difficult and you will have to do this in VBA
You want the text to color accordingly in both columns B and C?
I'll see what I can do for the VBA part but it won't be before late this afternoon.

[Keebellah]

My answer crossed your post.
Like I said, I'll try. But will you only be typing in Column B ?

[Endlessfacepalms]

Thank you for taking time to help me with this.
Column C is for calculating the Revenue or Loss between 2 days.

Column B shows how much money there is in the Bank at the given date which you can see all the way to left.
Column C shows the revenue or loss between two days.


EDIT: Correction to the above.

If you take a look at column C3.
It is taking the value of B3 minus B2 = how much revenue was made. And give the result of the calculation their respective currency letters and coloring just like in column B.

And again for C4.
It takes the value of B4 minus B3 = how much revenue was made. And give the result of the calculation their respective currency letters and coloring just like in column B.
In C4's case, there is 0 revenue because there was no new earnings. The money in the bank didnt increase or decrease. Therefore cell C4 spits 0 out.

[Keebellah]

Yes I noticed that, I suggest you change the formula in C starting in row 3

Please Login or Register  to view this content.

You do not need calculations if the value in B3 = empty or 0?

[Endlessfacepalms]

See my previous message.

I placed the formula in C3, but it gave me an error "Formula doesnt contain recognizable text" #NAME?

[Endlessfacepalms]

If you take a look at column C3.
It is taking the value of B2 minus B1 = how much revenue was made. And give the result of the calculation their respective currency letters and coloring just like in column B.

And again for C4.
It takes the value of B3 minus B2 = how much revenue was made. And give the result of the calculation their respective currency letters and coloring just like in column B.
In C4's case, there is 0 revenue because there was no new earnings. The money in the bank didnt increase or decrease. Therefore cell C4 spits 0 out.

[Keebellah]

Yes I understood that and I expected a little more common sense on your part
Here's the file with the formula inserted, and do NOT start in in the first row because there is NO previous value

[Endlessfacepalms]

I know that I'am terrible at explaining things, therefore I just wanted to make shure that I did it correctly.
I have the utmost respect for your skills and knowledge.

Thank you

[Keebellah]

Coloring cell text separately will not work with numbers and less with the number format settings.
I managed something but column C will not work because it's a formula, it will be possible if the calculation is done with the same macro.
Right now I did the following.
You enter the numeric value in column B and the macro will convert it to text using the numberformat you set in that cell, that is important, that the numberformat be present.

With that set the coloring works.
I have attached the file with the macro and some tests.
The vba module with the extractnumber udf is necessary so the calculations can work, it extracts the number from the text in column B

Try this first and see if it works (only for column B)

[Endlessfacepalms]

Column B is working perfectly :D
I dont think it's worth fiddling with the coloring in column C. But if you take it as a challenge, you are more then welcome to do so

Can you take a look at C6 and C7.
its not "spreading" the results as it does with the numbers above it. Is it because the result is negative?

[Keebellah]

True, that's the reason and the number formatting does not allow more conditions, there is no if the else possible in number format. you would probably have to always process it as absolute value and then multiply by -1 the result should be negative but my guess is that that will not work.

[Endlessfacepalms]

Does it mean that there is nothing to do about it?

[Keebellah]

Not this way (I think) you'll have to check where you found the number format you used, they might have some idea
There might be alternative but you'll have to sort that out,
- a helper column for the calculations (may be hidden) and a results column showing the correct result
- fully macro processing?
You'll have to invest some time in vba coding

[Endlessfacepalms]

Alright Thank you very much for the help
you gave me some information that I can research on and move from there

[Keebellah]

It went faster than I expected, I'll let you research, and advice you do so, VBA is very versatile and you require a lot of imagination, insensitivity and a lot of time and patience (troubleshooting)
I modified your file. The formula is now in a hidden column D with just a number format to show large + and - numbers but not the 0 values
The macro takes the value in that column and transforms it and if it's negative the - sign is added as prefix.
If the tables on the right are dependent of these columns you will have to keep in mind that there is a hidden column (D) in between.

You can continue and fine-tune from here.

[Keebellah]

I think I got it working with negative and positive values

[Endlessfacepalms]

I am already watching some youtube tutorials

I took a look at both of your latest uploads.
The +/- are working great.
But now we have a new issue with the coloring.
It seems to get confused regarding the coloring when the value is negative.

[Keebellah]

Not when I tested here , did you try file 3?

[Endlessfacepalms]

Yes I did it with file 3.
I have attached file 3. And in case that you cant see the error on your side, i also attached a screenshot.

[Keebellah]

Found it, and corrected it, it's the ' in front of the - which is required to show the string with negative values,

[Endlessfacepalms]

I can confirm thats its working on this side too
Is it possible to make the calculation thingy working all the way down to row 366?

[Keebellah]

You can do that yourself.
loo at the vab code in the akt worksheet

Please Login or Register  to view this content.

This is the are it checks now so .... yes you got it change this to B366, wow, you'r e a genius.

BUT

First you must unhide column D

Then select the the cells of the last row as shown in the figure and with the left mouse button (red arrow) click and drag the rows down to 366, this will copy the formatting and formulas all the way down.
Hide column D and voila

[Endlessfacepalms]

At least we share the same opinion regarding your genioutisy :P
I changed it to 731 instead of 366. and got this error.

[Keebellah]

Yes, and I was expecting that much. the reason is now that you have to edit the vba code to do all your calculations, since the values in between are no 0 but blank then you get the error finding what to add.
It's the fact that the row above is blank, it does not recognize it as zero, or you add a check to avoid this and do no calculation or else place 0 in the cell above before calculating (via the macro)
And my next question, why the dates and empty rows? if you have no entry for a date why enter the row?
You see VBA and programming in general is taking into account ALL options and what do with what condition

[Endlessfacepalms]

It was caused by the date format
Does it get confused because because there is 2 or more dates with the same day number?

but if there is no numbers on the abouve cell it will give an error. Which is fair, because there must be a number no matter how low the bank account falls down.

EDIT: I thought the above was the problem, but I messed it all up now

[Keebellah]

The date has nothing to do with it because it's not taken into account in the vba code.
I'll check later this weekend i'm going out now,

[Endlessfacepalms]

Have a good weekend

[Keebellah]

You too, enjoy and test this one

[Endlessfacepalms]

You are an Excel legend! :D
Thank you for spending your time on helping me
Every thing is working perfectly

[Keebellah]

You're welcom