Find formatted cells and add row under each instance

[LoriD]

I have a sheet that I have used conditional formatting to highlight cells that meet a criteria. Can Excel find the formatted cells and add a blank row under it?

I have a dynamic range, columns A:J. I have recorded a macro and copied it into the worksheet change event that goes each time to the range, and applies the formatting. (I don't know if that's really smart, I'm open to suggestions.)

The condition is in column J, where I have this formula: =ISODD(COUNTIF($I$2:I7,1))
It is set up to format rows where this condition returns TRUE

 

[Damon Ostrander]

Hi Lori,

To answer your question, only VBA can add a row to a worksheet automatically. And I don't believe VBA can test directly to see if conditional formatting has set a cell color, etc. VBA can only test to see if the condition is met by doing its own test on the condition. Then it can add the row.

In reading the rest of your question, however, it is not clear to me that you are using cell built-in Conditional Formatting, or are already using VBA to test the condition and set the cell format. If the latter, it would be quite easy to add a line of code to add the needed row to the worksheet. VBA CAN test cell formatting that is not caused by Conditional Formatting.

I hope this helps.

Damon

 

[LoriD]

Damon,
This is how I get the conditional formatting:

Application.Goto Reference:="data"
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$J1=TRUE"
Selection.FormatConditions(1).Interior.ColorIndex = 35

This is in the worksheet change event, and is preceded by some code that places formulas in (the current row) of column J:

Cells(Target.Row, 10).Formula = _
"=isodd(countif($I$2:I" & Target.Row & ",1))"
Application.EnableEvents = True

I don't know if you can help me any more with this, but either way, thanks for the prmot response. (I am not at all good with VBA.)

 

[Damon Ostrander]

Hi again Lori,

As usual, answers just beget more questions (sorry). But now I understand that you are installing a Conditional Format condition at run time.

1. Why install it at run time (via code) rather than just entering it manually?

2. You mentioned adding a blank row after "formatted cells" (plural), yet your code only formats a single cell or range of cells named "data." Is data a range of multiple cells? Is it a disjoint range?

Since you are using true Conditional Formatting, the macro that inserts the rows must look at the conditions, not the cell color, in determining whether to add a row or not.

If data is a single cell, then the code that adds the row if the condition is met should look like:

If [data].Value = ([J1]=True) Then
[data].Offset(1).EntireRow.Insert
EndIf


Damon