Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Change Color Of Blank Cells

  1. #1
    Join Date
    28th January 2008
    Posts
    6

    Change Color Of Blank Cells

    Hello all,

    My query is probably quite simple but I am not very good with forumlae within Excel but would appreciate any help anyone can offer.

    I am wanting to change the background of a cell to red if the cell is blank - Basically this is to highlight in a form any missed important areas...

    Thanks in advance.

    Geoff

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,491

    Re: Changing Cell Colour Based On A Condition

    hi Geoff,

    Welcome to the forum.

    If you want to highlight a cell, use conditional formatting. This is easy enough to do and if you search the forum or look at the possible answers section above, you should find what you are looking for.

    If you want to colour in a text box on a Userform, then this is different and you will need to use VBA. Post back if you need more information.

    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  3. #3
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,612

    Re: Changing Cell Colour Based On A Condition

    In the Conditional Format as mentioned, for cell A1 use =IF(ISBLANK(A1),1,0) and select the pattern you want

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,491

    Re: Changing Cell Colour Based On A Condition

    use =IF(ISBLANK(A1),1,0)
    For the record, since ISBLANK returns true or false, you dont need an IF statement...

    This will work just as good -

    =ISBLANK(A1)

    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  5. #5
    Join Date
    28th January 2008
    Posts
    6

    Re: Changing Cell Colour Based On A Condition

    Thank you for the replies.

    I need this formatting to automatically highlight the blank cells red though.

    If I follow the suggested CF as suggested, it is only highlighting the cell that I select...

    Many thanks

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th October 2006
    Location
    London
    Posts
    160

    Re: Changing Cell Colour Based On A Condition

    Select your entire range and apply the conditional formatting as before. Using a relative reference in the formula means it will adjust for each cell.

    Dave

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,612

    Re: Changing Cell Colour Based On A Condition

    If you use the previous formula ISBLANK(A1), there is already a relative reference, you just need to copy the cell where is the CF (here A1) and make a Special Paste Format to others cells

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    28th January 2008
    Posts
    6

    Re: Changing Cell Colour Based On A Condition

    This isn't working for me I'm afraid...

    If I apply the above suggested conditional formatting, it will shade the background of cell A1 but then if I try and drag this across a range of cells (some with content and some without) then it is shading all the selected cells and clearing any content in the cells...

    If I try and drag this across the table in question, where I am hoping it will shade any blank cells the colour I selected in the conditional formatting, it just produces an error stating that the operation requires the merged cells to be identically sized...

    This maybe something I am doing incorrect however. :S

    Many thanks

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,491

    Re: Changing Cell Colour Based On A Condition

    I would Strongly advise against merged cells, they cause nothing but trouble.

    If you can remove the merged cells then with the conditional formatting on one cell (A1):

    Copy the cell A1
    Select the area that you want to apply the conditional formatting to
    Right click in that selected area and select "Paste Special"
    Select "Formats"

    For the record, when I did this on an area that contained merged cells, the cells "unmerged".

    HTH
    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  10. #10
    Join Date
    28th January 2008
    Posts
    6

    Re: Changing Cell Colour Based On A Condition

    Thanks Ger, I have applied your suggestions and this is working perfectly now.

    Thank you ever so much for your help.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Color Duplicate Non Blank Cells In Column
    By Diego Garcia in forum EXCEL HELP
    Replies: 3
    Last Post: August 4th, 2008, 08:30
  2. Replies: 7
    Last Post: April 21st, 2008, 19:47
  3. Change Font Color If Another Cell Is Blank
    By jenneshell in forum EXCEL HELP
    Replies: 7
    Last Post: January 21st, 2008, 23:39
  4. Replies: 5
    Last Post: January 10th, 2008, 01:30
  5. Replies: 3
    Last Post: February 4th, 2005, 21:16

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
  •  
porno