Hello Experts,

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.Hi

dden = True

Else

Rows("28:31").EntireRow.Hidden = False

End If

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.

Thanks,

Sample-v.1.xls