Find formatted cells and add row under each instance

LoriD

Board Regular
Joined
Apr 1, 2002
Messages
141
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Board Regular
Joined
Apr 1, 2002
Messages
141
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

Forum statistics

Threads
1,148,195
Messages
5,745,285
Members
423,942
Latest member
excelhelp1423

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top