+ Reply to Thread
Results 1 to 7 of 7

VBA code to change background color based on another fields value

  1. #1
    Registered User
    Join Date
    10-29-2013
    Location
    America
    MS-Off Ver
    Excel 2003
    Posts
    4

    VBA code to change background color based on another fields value

    I need to write VBA code to change the background color based on another cells value. The reason why I need to use VBA code instead of Conditional Formatting is because later I will be writing a macro to clear the contents of the cells based on the cell color (gray or not). My current code is shown below, however, it seems as if section of B59/B60 and B71/B72 are conflicting as the function can only handle one of the same logic sets? Same thing is happening for B65/B66 and B73/B74, the first case "CBC 2001" isn't working in B73/B74 as it should, however all other cases are working. Is this because they are both in the same Sub? Is there a way around this?

    If not, is there another way to write the conditional formatting so I can write two seperate Sub's and both will be working?

    Thanks

    Please Login or Register  to view this content.
    In this code, if it's not understood properly, for the first set, it looks at B13. If SC Bob is selected, it turns B20 to color index 16 (Gray)

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: VBA code to change background color based on another fields value

    I would guess you need to have two separate if statements instead of elseif's.

    Could describe in more detail what you are doing and the logic behind your code. I'm not sure I follow your reasoning for using a macro to change the cell colour - do you just want to highlight all the grey cells based on a condition and then delete all the grey cells? Can't you just delete all the cells that meet your condition?

  3. #3
    Registered User
    Join Date
    10-29-2013
    Location
    America
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: VBA code to change background color based on another fields value

    I would guess you need to have two separate if statements instead of elseif's.

    Could describe in more detail what you are doing and the logic behind your code. I'm not sure I follow your reasoning for using a macro to change the cell colour - do you just want to highlight all the grey cells based on a condition and then delete all the grey cells? Can't you just delete all the cells that meet your condition?
    I was originally using Conditional formatting, so based on a fields value, I wanted to change the background of another field. For instance, if B13 = "SC Bob", I wanted to change B20's field to a gray background. The problem with this was, I later wanted to be able to clear the contents of this field, so I wrote a macro

    Please Login or Register  to view this content.
    However, Excel doesn't recongnize Conditional Formatting as actually changing the Interior.ColorIndex, so I was seeing it as Gray, although the ColorIndex was still -4142 (No Fill), so I had to write VBA code to do my conditional formatting so it would ACTUALLY change the colorindex.

    EDIT: I also tried doing If instead of ElseIf and it didn't change B71/B72 and it only messed up B59/B60 as it made both cases (III and Other) gray
    Last edited by SZIMM; 11-18-2013 at 11:44 AM.

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: VBA code to change background color based on another fields value

    I meant, use the conditional formatting statement as a check, not the colorindex, so if your conditional formatting check was, for example, if the cell value was greater than 10 set to grey, your code would be:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-29-2013
    Location
    America
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: VBA code to change background color based on another fields value

    Quote Originally Posted by yudlugar View Post
    I meant, use the conditional formatting statement as a check, not the colorindex, so if your conditional formatting check was, for example, if the cell value was greater than 10 set to grey, your code would be:
    Please Login or Register  to view this content.
    I have a ton of different conditional formatting done in VBA, I only showed a few samples. Would I be able to keep my color index macro, and then make additional macro's (5 or 6 cases where there are duplicates in the original function not working) and have them run on start like the Worksheet_Change sub?

    So are you referring to something like this?

    Please Login or Register  to view this content.
    ^^Is that even right? Is that how you judge one cell by another cells value? If not, can you please help me with the code?
    Last edited by SZIMM; 11-18-2013 at 11:59 AM.

  6. #6
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: VBA code to change background color based on another fields value

    Your code in the last post is:
    Please Login or Register  to view this content.
    if you want to do it for a group of cells it would be:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    10-29-2013
    Location
    America
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: VBA code to change background color based on another fields value

    Quote Originally Posted by yudlugar View Post
    Your code in the last post is:
    Please Login or Register  to view this content.
    if you want to do it for a group of cells it would be:
    Please Login or Register  to view this content.
    Thank you very much yudlugar, this solution works perfectly! I appreciate the help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Change the font and background color of the VBA code screen
    By niko79542 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: Yesterday, 09:54 PM
  2. [SOLVED] VBA code to change background color of a row in a spreadsheet
    By michelle27800 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-15-2012, 10:09 AM
  3. Need message box code to change background color in macro
    By BGiffin2004 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-01-2011, 03:32 AM
  4. change text color based on background color
    By great.bean in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-05-2009, 07:07 PM
  5. Modify Code to change cell background color
    By dcgrove in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-12-2008, 12:54 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1