# changing the color of a cell based on content

1. ## changing the color of a cell based on content

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

2. ## Re: changing the color of a cell based on content

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?)

3. ## Re: changing the color of a cell based on content

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?

4. ## Re: changing the color of a cell based on content

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

5. ## Re: changing the color of a cell based on content

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

6. ## Re: changing the color of a cell based on content

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?

7. ## Re: changing the color of a cell based on content

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?

8. ## Re: changing the color of a cell based on content

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

9. ## Re: changing the color of a cell based on content

Thank you very much, but the first one works better

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1