I have a fillFormula macro, but I want to include something in it to 1) apply a conditional formatting to Range("C7:C" & Lr) that will look for duplicate values and apply a red-bold format to each, and 2) to set column C to a text format so that it will stop dropping leading zeros.
Also, I currently have 4 columns shaded pink and an instruction in the page header to not edit pink cells, but is there a way to add automatic cell protection to these cells so that only macros can modify them/no one can just click on the cell and start editing? On a similar note, sometimes this macro miscounts and starts applying formulas in row 6, instead of row 7, which overwrites the column headings. Can I apply a cell protection to rows 5 and 6 so that the macro can't modify these cells (I'm not as worried about human modification here, but if complete protection is best, that's ok too)?
Also, I currently have 4 columns shaded pink and an instruction in the page header to not edit pink cells, but is there a way to add automatic cell protection to these cells so that only macros can modify them/no one can just click on the cell and start editing? On a similar note, sometimes this macro miscounts and starts applying formulas in row 6, instead of row 7, which overwrites the column headings. Can I apply a cell protection to rows 5 and 6 so that the macro can't modify these cells (I'm not as worried about human modification here, but if complete protection is best, that's ok too)?
Code:
Sub fillFormula3()
Dim Lr As Long
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Main Data")
Lr = ws1.Range("A" & Rows.Count).End(xlUp).Row
ws1.Range("E7:E" & Lr).Formula = "=IF(D7="""",D$4,D7)"
ws1.Range("F7:F" & Lr).Formula = "=IF(G7="""",G$4,G7)"
ws1.Range("M7:M" & Lr).Formula = "=SUM(I7:L7)"
ws1.Range("N7:N" & Lr).Formula = "=IF(M7="""","""",(H7-M7))"
ws1.Range("A7:B" & Lr).Interior.Color = RGB(248, 203, 176) 'light pink
ws1.Range("M7:N" & Lr).Interior.Color = RGB(248, 203, 176) 'light pink
With ws1.Range("A7:N" & Lr).Borders[INDENT].LineStyle = xlContinuous
.ColorIndex = 11[/INDENT]
End With
With ws1
[INDENT]With Application.ErrorCheckingOptions[/INDENT]
[INDENT=2].BackgroundChecking = False
.EvaluateToError = False
.InconsistentFormula = False[/INDENT]
[INDENT]End With[/INDENT]
End With
End Sub