Format Cells on Change Event

[julhs]

I know my Change Event code completely lacks the necessary code to format any cells, but that is part I’m struggling with.
The Xl2bb should be able demonstrate far better than I can explain as to what it is I’m trying to achieve.
In a nut shell; if you insert cells in say B10:C10 it leaves the last cell in ColA without any formatting but I want it formatted
the same as last cell ColA -1.

Test Insert.xlsm
	A	B	C	D	E
5
6	This Range of cells can NOT have cells inserted into it	Data	Data	When I manually insert cells in B6:C23 and use "Shift Down", I also want to automatically format A30+1 to take into account the new extended range B6:C24.
7		Data	Data
8		Data	Data
9		Data	Data
10		Data	Data
11		Data	Data
12		Data	Data
13		Data	Data
14		Data	Data
15		Data	Data
16		Data	Data
17		Data	Data
18		Data	Data
19		Data	Data
20		Data	Data
21		Data	Data
22		Data	Data
23		Data	Data
24		Data	Data	Of coarse if I manually insert cells in A25:C30, the new cells are formated to the same as cells above
25		Data	Data
26		Data	Data
27		Data	Data
28		Data	Data
29		Data	Data
30		Data	Data
31
Sheet1

 

[julhs]

Would help if I includede the Change Event

Private Sub Worksheet_Change(ByVal Target As Range)
   Application.EnableEvents = False
    If Not Intersect(Target, Range("Chem_Test")) Is Nothing Then
     ActiveSheet.[$A$30].Select
  End If
   Application.EnableEvents = True
End Sub

 

[julhs]

Give me strength!!
"Chem_Test" is named range "Sheet1!$B$6:$C$30"

 

[julhs]

If it’s of any help to someone in the future I eventually came up with something that did what I wanted.
Don’t doubt a more capable person would be able to come up with something more concise.

Test Insert.xlsm
	A	B	C	D	E
5
6	Array formula here	Data	Data
7	Array formula here	Data	Data	When I manually insert cells in B6:C23 and use "Shift Down", I also want to automatically format A30+1 to take into account the new extended range B6:C30+1.
8	Array formula here	Data	Data
9	Array formula here	Data	Data
10	Array formula here	Data	Data
11	Array formula here
12	Array formula here
13	Array formula here
14	Array formula here	Data	Data
15	Array formula here
16	Array formula here	Data	Data
17	Array formula here	Data	Data
18	Array formula here
19	Array formula here	Data	Data
20	Array formula here
21	Array formula here	Data	Data
22	Array formula here	Data	Data
23	Array formula here	Data	Data
24				Of coarse if I manually insert cells in A25:C30, the new cells are formated to the same as cells above
25
26		Data	Data
27
28
29		Data	Data
30		Data	Data
31
Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim LastRow As Long
 Dim rng As Range
        Application.ScreenUpdating = False
        Application.EnableEvents = False   
  If Not Intersect(Target, Range("Chem_Test")) Is Nothing Then
     LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, _
                 SearchDirection:=xlPrevious).Row
      Call ReFormatRange  'Is in this Module
    End If
  If Not Intersect(Target, Range("ArrayFormula")) Is Nothing Then
     LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, _
                 SearchDirection:=xlPrevious).Row
      Call ReFormatRange  'Its in this Module
    End If
  Set rng = Range("A" & LastRow + 1)
   rng.Select
        Application.ScreenUpdating = True
        Application.EnableEvents = True
End Sub
'---------------------------------------------------------------------------------
Sub ReFormatRange()
 Dim LastRow As Long
 Dim LastRow1 As Long
        Application.ScreenUpdating = False
'----
'# This finds LastRows and then "ReFormats" ColA to Lastrow1(ColB)if rows are ADDED or DELETED in B6:C25
   With ActiveSheet
     LastRow1 = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious).Row
     LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, _
                 SearchDirection:=xlPrevious).Row
     LastRow = ActiveSheet.Range("A40").End(xlUp).Row 'was LastRow1
      .Range("A" & LastRow1 & ":A" & LastRow + 1).Select
        Selection.FillDown
'----
'# This "ReFormats" (LastRow1 ColA:LastRow1 ColC) down if rows are DELETED in ColB:ColC
'#   it will ALSO reformat ColA if "ArrayFormula" NamedRange has rows INSERTED or DELETED
      .Range("A" & LastRow1 + 1 & ":C" & LastRow1 + 5).Select
        With Selection.Interior
           .Pattern = xlNone
           .TintAndShade = 0
           .PatternTintAndShade = 0
    End With
'----
'# This "ReFormats" ColA if a row is INSERTED immediatly below "ArrayFormula" NamedRange
'#   ,ie "ArrayFormula" NamedRange +1
      .Range("A" & LastRow + 1 & ":A" & LastRow1).Select
        With Selection.Interior
           .Pattern = xlSolid
           .PatternColorIndex = xlAutomatic
           .ThemeColor = xlThemeColorAccent3
           .TintAndShade = 0.599993896298105
           .PatternTintAndShade = 0
        With Selection.Font
           .ColorIndex = xlAutomatic
           .TintAndShade = 0
   End With
  End With
 End With
        Application.ScreenUpdating = True
End Sub

 

[julhs]

NamedRanges
"ArrayFormula" =Sheet1!$A$6:$A$23
"Chem_Test" =Sheet1!$B$6:$C$30