Color a cell If 2 other cells meet condition, undo cell color if third cell meet condition

**aftbrah** · 10-25-2016, 10:10 PM

I have a legend with 5 specific names in a small range of cells which individually get highlighted based on conditions. However, I have rows from column A to G, that go all the way down to row 300. The columns I'm only concerned about for conditionally formatting A7:A11 are columns C16:C300, E16:E300 and G16:E300.

If column C contains value "John", and column E contains value "PAYMENT PENDING", then color cell $A$7 YELLOW
If column C contains value "Alex", and column E contains value "PAYMENT PENDING", then color cell $A$8 YELLOW
If column C contains value "Smith", and column E contains value "PAYMENT PENDING", then color cell $A$9 YELLOW
If column C contains value "Tommy", and column E contains value "PAYMENT PENDING", then color cell $A$10 YELLOW
If column C contains value "James", and column E contains value "PAYMENT PENDING", then color cell $A$11 YELLOW

I also need a different color ORANGE when column E contains value "PAYMENT PROCESSING" for the same conditions above.

To clarify:

If column C contains value "John", and column E contains value "PAYMENT P PROCESSING", then color cell $A$7 ORANGE
If column C contains value "Alex", and column E contains value "PAYMENT P PROCESSING", then color cell $A$8 ORANGE
If column C contains value "Smith", and column E contains value "PAYMENT P PROCESSING", then color cell $A$9 ORANGE
If column C contains value "Tommy", and column E contains value "PAYMENT P PROCESSING", then color cell $A$10 ORANGE
If column C contains value "James", and column E contains value "PAYMENT P PROCESSING", then color cell $A$11 ORANGE

and then condition to undo color fill:

If column G contains any value, and column C contains value "John" while column E contains value "PAYMENT PENDING" or "PAYMENT PROCESSING", then undo color for cell $A$7
If column G contains any value, and column C contains value "Alex" while column E contains value "PAYMENT PENDING" or "PAYMENT PROCESSING", then undo color for cell $A$8
If column G contains any value, and column C contains value "Smith" while column E contains value "PAYMENT PENDING" or "PAYMENT PROCESSING", then undo color for cell $A$9
If column G contains any value, and column C contains value "Tommy" while column E contains value "PAYMENT PENDING" or "PAYMENT PROCESSING", then undo color for cell $A$10
If column G contains any value, and column C contains value "James" while column E contains value "PAYMENT PENDING" or "PAYMENT PROCESSING", then undo color for cell $A$11

NXuHU0k.jpg

Basically, I just want the cells in $A$7:$A$11 to be colored conditionally and then undo coloring when a later condition is met. Keep in mind, I begin at row 16 and gradually fill out the rows down to row 300 throughout the day, so I want to be able to see the cells in the legend get conditionally highlighted only when I enter the values mentioned above.

I know this is a tall order, and I have asked in another forum but with no luck thus far... I'm hoping VBA might be the answer to my problem. Thank you so much for any help!

**vichopalacios** · 10-27-2016, 10:20 AM

@ aftbrah

You can try with this code,
it will act every time you change a value in your data table
it recognizes automatically the size of the table

You'd copy and paste the code into worksheet module corresponding to your page:
.- press Alt F11 to open de Editor
.- Doble click on "Sheet1(Instructions)
.- Paste de code

then just play around to see if it's OK

**vichopalacios** · 10-27-2016, 10:21 AM

Sorry, forgot to attach the code:

Please Login or Register  to view this content.

**aftbrah** · 10-27-2016, 10:42 AM

hmmm I entered that code but it did not work. No error messages or anything, just simply nothing happens when I meet the conditions.

**vichopalacios** · 10-27-2016, 01:46 PM

weird,
It's working for me !
did you placed the code in the proper sheet ?
Captura.JPG

**aftbrah** · 10-27-2016, 03:56 PM

Here's a screenshot of mine. I did the exact same

paymentpending.jpg

Doesn't work for me :/

**vichopalacios** · 10-27-2016, 05:45 PM

In your first screenshot you placed:
List of Names in A7:A11

and your table was:
list of names C16:C..."lastData" that is column 3
list of PAYMENT E16:E..."lastData" that is column 5
list of complete G16:G..."lastData" that is column 7

I used this positions in the code I posted

In my screenshot I used columns C,D,E but I posted for yours. so don't care about "my columns position"... use yours C,E,G which are 3,5,7 according to the code you have.
Sorry if I mislead with my image.

**aftbrah** · 10-27-2016, 06:04 PM

Hmmm.. i tried it but it partially works. It misses it half the time and then I have to re-input the values in the cells hoping it works again. I'm not sure but it might be the specific conditions in the script that don't account for certain events. This is a running document, so the values are entered on-the-go. I don't know if that helps.

Thank you for all the help so far though, EXTREMELY appreciate your generous time

**vichopalacios** · 10-27-2016, 08:16 PM

I appreciate your words.I'm happy helping people...feel free to ask.

The worksheet change event acts every time you change by direct action the value of any cell in columns C E and G within the range of the table.
It does not act when the changes are a result of formula calculations.....
If you tell me how your sheet works, we can find a way to solve.... in Excel almoxt everithing is possible.
I don't follow what "certain events" and "values are entered on the go" means.

**aftbrah** · 10-27-2016, 08:33 PM

Okay I think I figured out the nature of the bug. When I begin backwards, then it works. For example, if I enter John in C18, PAYMENT PENDING in E18, then go to next row up (C17, E17) and enter information, then it works. But when I begin from row 16 and go down the table it doesn't work. Its weird but that is what is happening now.

Basically, I think the script is reading it backwards (C300:C16, E300:E16, etc) instead of the other way around (C16:C300, E16:E300, etc). That is just my guess, but I am not sure how that makes sense either lol

**vichopalacios** · 10-27-2016, 09:45 PM

The code itself has nothing to do with the order you input data.
It just is triggered whenever you change the value in a cell within the range.
Do you always input data in certain order? For instance first C then E...if it is so, we can suspend the control over C, and keep controling E and G.
Then you can replace this line
If Target.Column = 3 Or Target.Column = 5 Or Target.Column = 7 Then
With
If Target.Column = 5 Or Target.Column = 7 Then

In my test it works OK...no diference with any order..
Now, I found a mistake while writing this.
Do not change the previoun row.(just forget it)
The code is not checking the last row...to solve the problem changethis line:
If Target.Row > 15 And Target.Row < lastData Then
With this:
If Target.Row > 15 And Target.Row < lastData +1 Then

I hope it will make the difference

**jindon** · 10-27-2016, 10:30 PM

Perhaps

Please Login or Register  to view this content.

**aftbrah** · 10-28-2016, 12:31 AM

Originally Posted by vichopalacios

The code itself has nothing to do with the order you input data.
It just is triggered whenever you change the value in a cell within the range.
Do you always input data in certain order? For instance first C then E...if it is so, we can suspend the control over C, and keep controling E and G.
Then you can replace this line
If Target.Column = 3 Or Target.Column = 5 Or Target.Column = 7 Then
With
If Target.Column = 5 Or Target.Column = 7 Then

In my test it works OK...no diference with any order..
Now, I found a mistake while writing this.
Do not change the previoun row.(just forget it)
The code is not checking the last row...to solve the problem changethis line:
If Target.Row > 15 And Target.Row < lastData Then
With this:
If Target.Row > 15 And Target.Row < lastData +1 Then

I hope it will make the difference

It works now thank you so much! I especially like that the cell in column C range is also highlighted. It makes it a lot easier to scroll through the table to figure out where "John" has a payment pending, etc. Quick question, is there a way to make it an array like the poster jindon has it? So that it isn't strictly the name in the cell, but as long as the cell contains the name.

Originally Posted by jindon

Perhaps

Please Login or Register  to view this content.

This works as well! Thank you so much. You guys at ExcelForum.com are awesome!

**jindon** · 10-28-2016, 01:03 AM

Originally Posted by aftbrah

I especially like that the cell in column C range is also highlighted. It makes it a lot easier to scroll through the table to figure out where "John" has a payment pending,

Please Login or Register  to view this content.

**aftbrah** · 10-28-2016, 01:12 AM

PERFECT! Thank you guys so much!

**jindon** · 10-28-2016, 01:17 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Color a cell If 2 other cells meet condition, undo cell color if third cell meet condition

LinkBack

Thread Tools

Rate This Thread

Display

Color a cell If 2 other cells meet condition, undo cell color if third cell meet condition

Re: Color a cell If 2 other cells meet condition, undo cell color if third cell meet condi

Re: Color a cell If 2 other cells meet condition, undo cell color if third cell meet condi

Re: Color a cell If 2 other cells meet condition, undo cell color if third cell meet condi

Re: Color a cell If 2 other cells meet condition, undo cell color if third cell meet condi

Re: Color a cell If 2 other cells meet condition, undo cell color if third cell meet condi

Re: Color a cell If 2 other cells meet condition, undo cell color if third cell meet condi

Re: Color a cell If 2 other cells meet condition, undo cell color if third cell meet condi

Re: Color a cell If 2 other cells meet condition, undo cell color if third cell meet condi

Re: Color a cell If 2 other cells meet condition, undo cell color if third cell meet condi

Re: Color a cell If 2 other cells meet condition, undo cell color if third cell meet condi

Re: Color a cell If 2 other cells meet condition, undo cell color if third cell meet condi

Re: Color a cell If 2 other cells meet condition, undo cell color if third cell meet condi

Re: Color a cell If 2 other cells meet condition, undo cell color if third cell meet condi

Re: Color a cell If 2 other cells meet condition, undo cell color if third cell meet condi

Re: Color a cell If 2 other cells meet condition, undo cell color if third cell meet condi

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find first cell meet a condition within a range

highlight cell if meet condition

[SOLVED] Toggle fill Color in cell based on condition of cell--blank or text added.

[SOLVED] Conditional format one cell if any cells in column meet condition

cell color fill depending on condition of 2 other cells

Show if any cell has meet condition

sum of a cell if 2 cells meet a condition

Bookmarks

Bookmarks

Posting Permissions