I am trying to utilize conditional formatting with VBA in Excel. The formatting is fairly basic, where it hides rows based on values in different cells. There is a VBA procedure that allows the user to add formatted rows in the middle of the Excel spreadsheet, thus changing the row reference. For example, if I use the following statement:
If LCase(Range("H6").Value) = "same" Then
Rows("28:31").EntireRow.Hidden = True
Rows("28:31").EntireRow.Hidden = False
If a new row is added, the rows are no longer 28:31, they will be 29:32
There are two questions:
Question 1. How do you reference rows in VBA that may change its position in the spreadsheet?
Question 2. How would I use VBA to look at multiple cells for conditional formatting?
Here is the conditional formatting rules:
1. If H6 is = SAME, then hide rows 28:31 (i.e. Questions 8-10 are hidden), else show rows 28:31
2. If J17 AND J18 is = NO, then hide row 19:22 (i.e. Questions 2-5 are hidden), else evaluate 2a & 2b below
a. If J19 is = YES, then hide row 20 (i.e. Question 3 is hidden)
b. If J19 is = NO, then hide row 21:22 (i.e. Question 4 & 5 are hidden)
3. If J33 is = NO, then hide row 34 (i.e. Hide Describe: cells below Question 12)
4. If J35 is = NO, then hide row 36 (i.e. Hide Explain: cells below Question 13)
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
-Mike Kapnisakis, Warner Bros
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.