Programatically changing background colour of cell

  • I have a excel document which has a chart of boolean values... ie 0's and 1's


    All I was is something that basically looks though all the cells and if its a number 1 then it makes the bg colour Green, or if its a 0 then red... and if its not a 1 or 2 then to ignore it...


    Any idea's??


    I have never ever done anything advanced in excel so please explain in simple terms if this is possible :)


    - Carl S

  • helping out


    This task is relatively easy to do. First you will need to create some kind of macro to perform the task for you.
    You can begin by clicking "Tools > Macros > Macros"


    Here, specify the name of the macro you want to create, then click "create"



    You will be presented with the VB interface. In the text file present, add the following code:




    The problem with the above code is that it is not yet optimized for your usage. It checks every possible cell in the worksheet, which will take a few seconds to do. What i would suggest is you add some kind of boudary to make the macro more efficient. For example:





    Above, the code will exit on row 100, making it a lot more efficient. You can change this number to whatever row you think you would go up to.


    The easiest way to call this code would now be to click "Tools > Macros > Macros" then select the macro from the list, and click RUN

  • conditional formatting


    choose conditional formatting from the format menue and there choose "cell value is" "equal to" your wanted value and then choose the corresponding formatting below.

    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

  • Hi Jong,


    To be fair the actual description of the forum is.
    For all questions relating to Excel and/or Excel VBA


    The call on whether to supply a VBA or worksheet solution can be a difficult one. Unless the OP explicitly specifies one or the other.


    The title for this post Programatically changing background colour of cell would suggest a VBA solution was required.
    But the closing remark, I have never ever done anything advanced in excel so please explain in simple terms if this is possible , MAY suggest that the OP was not aware of conditional formatting.


    Both replies are good, its up to the OP to decide whether they really wanted VBA or a new technique. Hopefully any feedback the OP gives will clarify that.

    [h4]Cheers
    Andy
    [/h4]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!