Announcement

Collapse
No announcement yet.

Change Color Of Blank Cells

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    Check out our new reputation system. Click on the "star" under the post!
    _______________________________________________

    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

    _______________________________________________

    Comment


    • #3
      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
      Triumph without peril brings no glory: Just try

      Comment


      • #4
        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

        Check out our new reputation system. Click on the "star" under the post!
        _______________________________________________

        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

        _______________________________________________

        Comment


        • #5
          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

          Comment


          • #6
            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

            Comment


            • #7
              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
              Triumph without peril brings no glory: Just try

              Comment


              • #8
                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

                Comment


                • #9
                  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

                  Check out our new reputation system. Click on the "star" under the post!
                  _______________________________________________

                  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

                  _______________________________________________

                  Comment


                  • #10
                    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.

                    Comment


                    • #11
                      Re: Changing Cell Colour Based On A Condition

                      No worries, and thanks for posting back that it worked out OK.

                      Ger

                      Check out our new reputation system. Click on the "star" under the post!
                      _______________________________________________

                      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

                      _______________________________________________

                      Comment

                      Working...
                      X