Cell Colour Depending On Value Entered

  • Hello VBA genius types. I need some help. I found the code below to do almost exactly what i want. However, i would like to know if it can be modified to only affect a specific cell range. In my case, the cell range is B9:AF37. Thanks in advance for any suggestions. (I am a complete novice with VBA :( )

  • Re: Cell Colour Depending On Value Entered


    The code below will do what you've asked but VBA is overkill. Also, this code runs every time anything on the worksheet changes. I would just use conditional formatting.



  • Re: Cell Colour Depending On Value Entered


    Hi Six Strings. Thanks for replying. I will test your code in a minute. I though conditional formatting was restricted to only 3 conditions?


    Maybe this is a limitation of Excel 2004 for Mac?


    Thanks for your time.

  • Re: Cell Colour Depending On Value Entered


    Hi


    I think there is a limit of 3 conditions using conditional formatting, so VBA is the way to go.


    The other point made by 6strings is valid so you may want to change it to a Sub function and run it whenever it is needed



    Robert

  • Re: Cell Colour Depending On Value Entered


    Thanks to both of you for replying. My understanding of VBA is very limited. Please be gentle!


    OK, i have tried the code provided by Six Strings. It does work eventually, but it doesn't work in the same way as the original code that i posted.


    With the suggested code, it appears that the conditions are being tested on every single cell in the range despite me only changing one of them. With the original code, the colour change was instant. Is it possible that the original code would only work on the cell that had been changed?

  • Re: Cell Colour Depending On Value Entered


    A somewhat simpler version



    Adjust for your specific cell range or specific columns.


    lenze

  • Re: Cell Colour Depending On Value Entered


    Hi Lenze. Thanks for taking the time to reply. Unfortunately, your code generates errors. The message given is that the variable Cell is not defined.


    I don't want to sound rude. But, is it not possible to modify the original code that i posted to work only within a specific cell range?


    I must also point out that the code is being used within Excel 2004 for Mac. I know that not all code for windows will work on Mac. The original code i posted works perfectly. That surprised the pants off me!!

  • Re: Cell Colour Depending On Value Entered


    this should do it. Change this line

    Code
    Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) 
    Set Rng1 = Range("B2:G20")


    with this


    Code
    Set Rng1 = Range("B2:G20")


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

  • Re: Cell Colour Depending On Value Entered


    Quote from Kevan


    I must also point out that the code is being used within Excel 2004 for Mac. I know that not all code for windows will work on Mac. The original code i posted works perfectly. That surprised the pants off me!!


    I use this approach on the Mac as well. The key is to program on the Windows side so that it works with XL 97, then for the most part it should work on Mac.


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

  • Re: Cell Colour Depending On Value Entered


    Quote from shades

    this should do it. Change this line

    Code
    Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) 
    Set Rng1 = Range("B2:G20")


    with this


    Code
    Set Rng1 = Range("B2:G20")



    Hi Shades. Thanks for your reply. I am a bit confused with the above. Are you telling me to replace both of the lines in the first section with the single line in the second section?


    Would you be kind enough to copy my original code, then post the modified code back here for me? I tried replacing the code as you suggested but i get a similar result to the other suggested mods. i.e The colour does in fact change eventually, but not until the entire sheet has been checked over by the code. As well as that, the colour coding is not restricted to the range of cells i entered :(

  • Re: Cell Colour Depending On Value Entered


    THANKS GUYS!!!


    With your help i have solved the problem. Here is the finished code.



    Special thanks to Lenze as this is clearly based on his suggestion.


    Lenze... one of the errors your initial code generated was that 'Cell' was not defined. You HAD defined 'Target' so i decided to change your only use of 'Cell' to 'Target' and it worked!


    I also added another Case so that when an entry is deleted, the cell interior goes back to blank. Without this line, the interior remained the colour defined by the deleted entry. I also had to include slight variations on two of the Cases (E- and L-). Rather than colour code the interior differently, i wanted to change the text colour from black to white.


    Thank you sincerely for all your help.

  • Re: Cell Colour Depending On Value Entered


    Glad it works for you. I did post it in a hurry and could not test it.
    FYI:
    The line "Dim cl as Range" is not needed. You are using Target instead which is a Range by default.


    Also, I really don't think you need to check for "xlNullString" as the "Case Else" should handle that. But hey, if it work's use it. It won't hurt.


    Cheers.


    lenze

  • Re: Cell Colour Depending On Value Entered


    Hello again guys. I need a little more help with this piece of code. It works perfectly in that it only works on a specific selection of cells on a page. However, i am using it on a 12 page spreadsheet. How do adjust the cell range to include specific ranges of cells from multiple pages?


    The following obviously refers to the specific cells on the first page.

    Code
    If Intersect(Target, Range("$AH$9:$BL$37")) Is Nothing Then Exit Sub


    How would i modify that to also include a specific range of cells from another page. The next cell range would be B48:AF76. There will obviously be 10 more specific cell ranges to include so that the code works on the 12 pages of my spreadsheet. Can someone show me how to expand the code above?


    Thanks.

  • Re: Cell Colour Depending On Value Entered


    If your cell ranges were the same on all sheets, you could transfer the code to the Thisworkbook module, using WorkBook_SheetChange. That, however, does not appear to be the case. You probably will need to copy the code into each sheet's individual module and then modify the Intersect statement in each module.


    lenze

  • Re: Cell Colour Depending On Value Entered


    Quote from lenze

    If your cell ranges were the same on all sheets, you could transfer the code to the Thisworkbook module, using WorkBook_SheetChange. That, however, does not appear to be the case. You probably will need to copy the code into each sheet's individual module and then modify the Intersect statement in each module.


    lenze



    Hi Lenze.


    I think you misunderstood what i meant. The spreadsheet (workbook) i am working on is a colour-coded staff shift rota and is set up as follows....


    One workbook containing 5 sheets with 12 pages of data per sheet + 1 summary page per sheet.



    There will be no interaction of any kind between sheets. But i do need to collate data from the 12 data pages within each sheet and present it on the 13th page within each sheet.


    The code you helped me bash out yesterday works perfectly. But, the cell range only takes in the first page (of 12). I needed to expand the cells the code takes in to cover specific cells on the remaining 11 pages.


    I discovered how do do this by recording a macro while i made specific cell selections over the various pages. The finished bit of code for that looks like this...


    Code
    If Intersect(Target, Range("$AH$9:$BL$37,$B$47:$AF$76,$AH$47:$BL$76,$B$87:$AF$115,$AH$87:$BL$115,$B$126:$AF$154,$AH$126:$BL$154,$B$165:$AF$193,$AH$165:$BL$193,$B$204:$AF$232,$AH$204:$BL$232")) Is Nothing Then Exit Sub


    This is now tried and tested and works!!


    I have now found some more code on 'that ther t'internet thing' that allows me to keep a count of the various colour coded cells over the 12 pages of the sheet. The results of this are presented on page 13. This page is simply a tally of how many of a particular shift type each staff member has worked. As the Rota also logs sick days and annual leave, it can be used to monitor sickness levels and ensure that staff remain within their allocated number of days of annual leave.


    I hope this ridiculously long , and probably boring, explanation puts you in the picture.


    Thanks again for your responses. Much appreciated.

Participate now!

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