+ Reply to Thread
Results 1 to 9 of 9

changing the color of a cell based on content

  1. #1
    Registered User
    Join Date
    12-18-2012
    Location
    reno nv
    MS-Off Ver
    Excel 2007
    Posts
    23

    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. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    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?)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    12-18-2012
    Location
    reno nv
    MS-Off Ver
    Excel 2007
    Posts
    23

    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. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-18-2012
    Location
    reno nv
    MS-Off Ver
    Excel 2007
    Posts
    23

    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. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    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. #7
    Registered User
    Join Date
    12-18-2012
    Location
    reno nv
    MS-Off Ver
    Excel 2007
    Posts
    23

    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. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    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
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-18-2012
    Location
    reno nv
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: changing the color of a cell based on content

    Thank you very much, but the first one works better

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Changing fill color of shapes based on changing cell value
    By Stefan1983 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-25-2012, 10:33 AM
  2. Group based on cell content or color
    By Shaner73 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-06-2012, 04:35 PM
  3. Tab color change based on cell content
    By sammi8796 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-02-2011, 02:15 AM
  4. Changing cell content based on button
    By vioravis in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-14-2008, 04:16 PM
  5. changing font color of cell content
    By Donald Stockton in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-09-2006, 01:55 PM

Bookmarks

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