VBA code to change background color based on another fields value

[SZIMM]

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)

[ragulduy]

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?

[SZIMM]

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

[ragulduy]

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.

[SZIMM]

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?

[ragulduy]

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.

[SZIMM]

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!