I have the following conditional that changes the color of a cell on the first worksheet when there is an "N" in the corresponding cell of another worksheet, called "Requirements":

=AND(Requirements!D11="N",D11<>"") 'the second piece of this conditional prevents blank cells from being formatted

I have a Sub for adding in a new column in both sheets and when I do so, the cell references for the new columns do not align.

I wanted to know if there is a way to add some code into my Sub that can redefine the range for the conditional formatting. My Sub for adding in the new rows is as follows:

Private Sub CommandButton2_Click()
'Add New Employee

Dim c As Long
c = Selection.Column

Columns(c + 1).Insert Shift:=xlToRight
Range(Cells(1, c + 1), Cells(9, c + 1)).Merge

Sheets("Requirements").Columns(c + 1).Insert Shift:=xlToRight
Sheets("Requirements").Cells(1, c).Copy
Sheets("Requirements").Cells(1, c + 1).PasteSpecial xlPasteFormulas

Application.CutCopyMode = False


End Sub