Hello,

So I've got a workbook that I use for creating checklists. I have a column that has the title "Staging / Lighting" which can move anywhere in the range H3:H200.

So the way it works is on another sheet of the workbook, I go through and click on different items in a long list that then populates this nicely formatted list on another sheet that can fit on one printed page. And as i'm clicking along, creating / deleting things that end up on the list, the "Staging / Lighting" title can move up and down, anywhere from H3:H200. I need VBA that can make "Staging / Lighting" size 16 font no matter where it is in H3:H200 at any given time, and everything else in that range to remain at size 9 font. I've gotten VBA to change the font size to 16, but as soon as it moves from its spot at the time the vba runs, it's font size reverts back to 9 and whatever ends up in it's cell becomes font size 16. I need "Staging / Lighting" to be size 16 font no matter where it's moved to in that range and as it moves around, everything else stays at 9.

I really wish Microsoft would allow you to adjust font size with their conditional formatting tool, but alas, they don't. Also, I'm on excel 2010.

Here's my current vba.


Sub FontAdj()

Dim i As Long

For i = 3 To 321

If InStr(1, Range("H" & i), "Staging / Lighting") Then

Range("H" & i).Font.Size = 16

End If

Next i

End Sub


thanks for your help!