changing the color of a cell based on content

[trisham]

Hi I have a spreadsheet that we use for billing, and I was hoping someone could help me figure this out. So in the example the first one is the data that is entered and the 2nd one is the result. If anyone can please help so that if i type if you have any questions about my example please ask i will be checking frequentlyEX 1.xlsx

[FDibbins]

I am a little confused

Where are you typing in a number, and where do you want the color to change (and based on what?)

[trisham]

ok so this is our billing spreadsheet and based on the log notes that our psr's turn it we add the numbers so if its 2 hours we input 2.00 under the correct date. and then the colors are representing the location which is either client home = h combo=b or community= m you will see the legend for that up in the right hand corner of the spreadsheet. so If they put 2 hours at the client home I would enter 2h and then later on come back and make it green and put 2.00 so what I want is for when i put 2h i want excel to automatically change it to 2.00with the cell filled in green. does that make more sense?

[FDibbins]

OK I understand now, thanks.

See if the attached will work for you? I have set it up so that you dont need to remove the trailing letter 0 in fact if you do, the CF wont work. I still gives you the total*4 inthe units column, even with the text still in the number

[trisham]

is there no way to do it to where it converts it to the 2.00 without the letter?

[FDibbins]

That will require VBA (not my strong side). Think about it - the CF is based on the letter (2b) as soon as you remove the letter, the CF has nothing to work with

Another option would be to have 2 columns for each day, you could enter 2 in the 1st and b in the 2nd, the CF could work off the 2nd cell, and you could format the 2nd cell to be white-on-white, so it actually looks invisible?

[trisham]

that one will work just fine, but on my actual spreadsheet I have about 200 more clients, how do I make it apply to all of them?

[FDibbins]

Take a look at the attached. We will have to do this in a few steps, but once its done, its done...

1. Add 2 columns AFTER each day (so 2 between E and F etc)
2. move or copy the Days to the 1st of the 2 new columns - I formatted them as "center across selection"
2. copy my formulas from (MY) F7:G7 to your live WB
3. Copy that all the way down F&G
4. Copy F&G to the newly inserted column-pairs
5. copy/paste values for the entire range (to get rid of the formulas, we dont need them now
6. delete your original data, you should just be left with the 2-column CF-days now

[trisham]

Thank you very much, but the first one works better