Conditional formatting with VBA for multiple conditions

Question

I have a spreadsheet with a number of sheets used by users. They are asked to input information in some of the sheets and the first Sheet is a summary of results. There are four conditions, if selected, lead to a pens down scenario where no further work needs to take place. Let's say if, Cells A1, B1, and C1 = "No", or if Col 1 in Sheet 2 contains "No", no further work needs to be done in Cells D12:K39, or some such range.

I thought running with conditional formatting may work, but the results are not great. Something like: =$A$1="No", and so on for the other conditions, then I colour out the selection range

I'm very new to VBA, but see this as way to get my results. I have no idea how to structure the above condition.

What code have you written so far to try and do this? Please share any code snippets that would help us to help you. — RyanNerd
– RyanNerd, Commented Oct 1, 2018 at 18:37

BigBen · Accepted Answer · 2018-10-01 17:53:47Z

1

Don't give up on conditional formatting just yet.

Select Cells D12:K39
Go to Conditional Formatting > Use a formula to determine which cells to format
Enter the formula =OR(COUNTIF($A$1:$C$1,"No")=3,COUNTIF(Sheet2!$A:$A,"No")>0)
Change the formatting as needed

answered Oct 1, 2018 at 17:53

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mathieu Guindon Over a year ago

Alternatively, select ONE of the cells you want to apply the format to; enter a conditional format formula that works for that one cell, absoluting rows and columns as needed ($) - create the rule, then in the "applies to" textbox pick D12:K39 to extend the conditional logic to the whole desired range.

premiumcopypaper Over a year ago

thanks, conditional formatting was my go to; the problem I have is I have other conditional formatting already taking place, and the colour schemes overlap

BigBen Over a year ago

Maybe you can manage conditional formatting rule precedence?

Mathieu Guindon Over a year ago

@premiumcopypaper order your rules accordingly (higher priority rules first); check the "stop if true" box

Collectives™ on Stack Overflow

Conditional formatting with VBA for multiple conditions

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related