VBA to enter various formulas every 10 rows and show dynamic formulas not values

Hi,

I'm trying to multiple macros in one file that will insert a formula and show not just the result but the formula when double clicking on the cells.
Once I have the formulas I want the macro to copy the formulas and paste it onto other lines (erery 10 lines)

I have added the file to explain better.

The formulas should appear between T6 : CQ(last row)
In my file I have entered the formulas I want in the first set of cells and highlighted each formula in a different color.

For example:
* I want cell T12 to show =T6-T7 then I want to copy this formula in cell T22 as = T16-T17 and so on until the last row. (My example only has a few row but I will add many more lines later on)

* I want cell U12 to show: =U6-U7+SUM(U8:U10). Then I want to copy this formula to each cell to the right (sort of like dragging a formula to the right), and then I want each row below to show the same combination ( The first cell T12 as =T6-T7 and all other cells as =U6-U7+SUM(U8:U10). (row 22, row32, row42 and so on)

Finally I want to enter a formula (the formula already in the first set of numbers - highlighted) in the first cell of each other row (Ex: T13, T14, T15 and U6) and then copy that formula across the row. Then copy in each 10 lines down until the end.

It's hard to read but when you open the file the question is much easier to follow.


Thank you so much for the help!

HI jaslake, the coding works wonderful in the file ...Thank you!

I just have one final question. The file I uploaded is not the original file of course. If any of the values between columns A and M change or the headers between columns N and Q change the macro should still work right?
I tried running it in the original file but I got an error Run-time error 91. Object variable or with block variable not set. The debug points at LR and LC.

Thanks

I figured out what's causing the error. Thank you for all the help Jaslake =)

Hi Jaslake,

I'm hoping you can help me take this file a step further.
I'd like to add a conditional formatting based on the values of variable cells compared to the values of other cells.

This shoud be true for every line with WOS. I'm using my previous file, for example row 13, 23 and so on)
I want each value on that row starting from column T to be compared to cells R6 and R12.
The formula should say if cell< R6 = background color should be red, cell> R6 and less than R6*0.5 = background color should be orange.
if R6*0.5>cell and < R12 = background color should be green, if cell> R12 = background color should be blue.

Thia set must repeat on every set below and therefore the constants R6 and R12 will chnage to R6 and R22 and so on.

I started by adding the following to this part of the macro but I'm getting an error;

Case "Inventory coverage (WOS)"
For Each cel In .Range("S2:S" & LR).SpecialCells(xlCellTypeVisible)
.Range("T" & cel.Row).Formula = "=T" & cel.Row - 1 & "/AVERAGE(U" & cel.Row - 6 & ":AF" & cel.Row - 6 & ")"
.Range("T" & cel.Row).AutoFill Destination:=.Range(.Cells(cel.Row, "T"), .Cells(cel.Row, "CQ"))
.Range("T" & cel.Row).FormatConditions.Delete
.Range("T" & cel.Row).FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, formula1:= "=IF(cell< (R" &cel.Row - 3 & "),.Interior.Color = rgbred, 0)" )

Next cel

Please let me know if I'm in the right track.

Thanks!

Hi Jaslake,

This is the recording for the first line. I want this for every ten lines. I'm attaching the excel file with the macro.

Thank you so much!!


Sub Conditional_Formatting()
'
' Conditional_Formatting Macro
'

'
Range(Selection, Selection.End(xlToRight)).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=$R$10"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=$R$10", Formula2:="=$R$10+(0.5*$R$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=$R$10+(0.5*$R$10)", Formula2:="=$R$12"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=$R$12"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 12611584
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

Sorry I'm still pretty new to this. I will try it and let you know. thank you!