Conditional Formatting all numbers two colors

  • Is it possible to write code so all even numbers have gray back ground and green font, and all odd numbers have red font with yellow background no matter how they placed on the sheet. I mean, if I tyoe in a 7 anywhere on my sheet I want it to be yellow and red, If I copy and paste from another sheet to this sheet its pasted with all even numbers have gray back ground and green font, and all odd numbers have red font with yellow background


    Thanks folks!!!


    [ATTACH=CONFIG]57842[/ATTACH]

  • Re: Conditional Formatting all numbers two colors


    Hi Larbec


    It is not really a job for vba. Conditional formatting is your puppy of choice as this is what it was designed for. Self explanatory...


    =IsODD(a2)



    =Iseven(A2)


    File attached to show workings.


    TheSmallman

  • Re: Conditional Formatting all numbers two colors


    @ smallman


    I know how to do the attached. I want to be able to place an even or odd number "anywhere" on the sheet and have it change. If you open your sheet attached and place an even number in any cell it does not change color

  • Re: Conditional Formatting all numbers two colors


    Hi larbec


    Read this Slow Response, Memory Problems, and Speeding up Excel

    You do not want Conditional Formating (CF) for an entire sheet as it will slow your workbook down to agrinding halt !
    Limit CF to the mimimum. better still aviod it.

  • Re: Conditional Formatting all numbers two colors


    Actually, only the CF in the visible scroll range are evaluated so it doesn't necessarily bog things down if a large area of CF is used.

  • Re: Conditional Formatting all numbers two colors


    This code added to each sheet where you want the formatting to apply will conditionally format any cell on the sheet that is either manually changed or changed using copy/paste. It is possible to paste a range of cells and the formatting will be applied to all.


    I have restricted the change that will trigger the formatting to a number between 1 and 9 inclusive, assuming that you only need the conditional formatting applied to those values, as in your data set.



    Hope this helps

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Conditional Formatting all numbers two colors


    Try this in a Worksheet Selection Change Event. As the other members pointed out it may slow your workbook down.


    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Conditional Formatting all numbers two colors


    Thanks y'all. I'm on my iPad but will check out later. I'm wanting to do this because some pages I copy, when I go to paste I Lose the option of pasting with values so I'm having to color the cells manually I've posted on another page fir help with that. You all simply amaze me with all your talent and expertise THANK YOU ALL,!!!

Participate now!

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