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
dden = True
dden = 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)
Attached is the sample Excel Spreadsheet.