Vba code required to perform certain actions on 1 Worksheet

[hsandeep]

X1, Y1 and Z1 are time value
H5, L5, P5 and T5=10 (by default)
H9:H16=10; L9:L16=10; P9:P16=10; T9:T16=10 (by default)
Vba code required for below actions

1. IF X1<now()<=Y1 Then
2. IF $B$2<=F5<=$B$1, Then IF F5 HasFormula Then Remove formula from F5 AND insert value 20 in $H$5. Also, Then IF F9:F16 HasFormula Then Remove formula from F9:F16 AND insert value 20 in H9:H16 (column H is 2 columns on the RHS of column F). Also, IF $B$2<=J5<=$B$1, Then IF J5 HasFormula Then Remove formula from J5 AND insert value 20 in $L$5. Also, Then IF J9:J16 HasFormula Then Remove formula from J9:J16 AND insert value 20 in L9:L16 (column L is 2 columns on the RHS of column J). Also, IF $B$2<=N5<=$B$1, Then IF N5 HasFormula Then Remove formula from N5 AND insert value 20 in $P$5. Also, Then IF N9:N16 HasFormula Then Remove formula from N9:N16 AND insert value 20 in P9:P16 (column P is 2 columns on the RHS of column N). Also, IF $B$2<=R5<=$B$1, Then IF R5 HasFormula Then Remove formula from R5 AND insert value 20 in $T$5. Also, Then IF R9:R16 HasFormula Then Remove formula from R9:R16 AND insert value 20 in T9:T16 (column T is 2 columns on the RHS of column R).
3. IF X1<now()<=Z1 Then
4. IF F5<=$B$3 AND H5=20, Then insert the formula back in F5 in R1C1 style AND fill H5 with 10 AND also insert the formula back in F9:F16 in R1C1 style AND fill H9:H16 with 10. Also, IF J5<=$B$3 AND L5=20, Then insert the formula back in J5 in R1C1 style AND fill L5 with 10 AND also insert the formula back in J9:J16 in R1C1 style AND fill L9:L16 with 10. Also, IF N5<=$B$3 AND P5=20, Then insert the formula back in N5 in R1C1 style AND fill P5 with 10 AND also insert the formula back in N9:N16 in R1C1 style AND fill P9:P16 with 10. Also, IF R5<=$B$3 AND T5=20, Then insert the formula back in R5 in R1C1 style AND fill T5 with 10 AND also insert the formula back in R9:R16 in R1C1 style AND fill T9:T16 with 10.
5. IF X1<now()<=Z1 Then
6. IF F5>$B$3 AND H5=10, Then Remove formula from F5 AND fill H5 with 20 AND also Remove formula from F9:F16 AND fill H9:H16 with 20. Also, IF J5>$B$3 AND L5=10, Then Remove formula from J5 AND fill L5 with 20 AND also Remove formula from J9:J16 AND fill L9:L16 with 20. Also, IF N5>$B$3 AND P5=10, Then Remove formula from N5 AND fill P5 with 20 AND also Remove formula from N9:N16 AND fill P9:P16 with 20. Also, IF R5>$B$3 AND T5=10, Then Remove formula from R5 AND fill T5 with 20 AND also Remove formula from R9:R16 AND fill T9:T16 with 20.
7. IF Z1<=now() Then
8. Insert the formula back in F5, J5, N5 and R5 in R1C1 style. Also, insert the formula back in F9:F16; J9:J16; N9:N16; R9:R16 in R1C1 style. Also fill H5 with 10, L5 with 10, P5 with 10 and T5 with 10. Also fill H9:H16 with 10; L9:L16 with 10; P9:P16 with 10 and T9:T16 with 10.

Copy vba.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	40250																						03/13/2023 9:30:00 AM	03/13/2023 9:31:00 AM	03/13/2023 3:30:00 PM
2	40101
3	40228				40100				40200				40300				40400
4
5					40100	40100	10		40200	40200	20		40300	40300	10		40400	40400	10
6
7
8
9		39900	800		800	800	10		800	800	20		800	800	10		800	800	10
10		40000	750		750	750	10		750	750	20		750	750	10		750	750	10
11		40100	630		630	630	10		630	630	20		630	630	10		630	630	10
12		40200	600		600	600	10		600	600	20		600	600	10		600	600	10
13		40300	532		532	532	10		532	532	20		532	532	10		532	532	10
14		40400	463		463	463	10		463	463	20		463	463	10		463	463	10
15		40500	302		302	302	10		302	302	20		302	302	10		302	302	10
16		40600	253		253	253	10		253	253	20		253	253	10		253	253	10
Sheet1

Cell Formulas
Range	Formula
F3,R3,N3,J3	F3	=F5
F5,R9:R16,N9:N16,J9:J16,F9:F16,R5,N5	F5	=G5
G9:G16	G9	=D9
K9:K16	K9	=D9
O9:O16	O9	=D9
S9:S16	S9	=D9

I have 1 incomplete code

Sub CheckAndUpdateValues()
    Dim nowTime As Date
    nowTime = Now()
    
    Dim startTime As Date
    Dim endTime As Date
    
    startTime = Range("X1").Value
    endTime = Range("Y1").Value
    
    If startTime < nowTime And nowTime <= endTime Then
        If Range("F5").Value >= Range("$B$2").Value And Range("F5").Value <= Range("$B$1").Value And Range("F5").HasFormula Then
            Range("F5").Value = 20
            Range("H5").Value = 20
            
            For i = 9 To 16
                If Range("F" & i).HasFormula Then
                    Range("F" & i).Value = 20
                    Range("H" & i).Value = 20
                End If
            Next i
        End If
        
        If Range("J5").Value >= Range("$B$2").Value And Range("J5").Value <= Range("$B$1").Value And Range("J5").HasFormula Then
            Range("J5").Value = 20
            Range("L5").Value = 20
            
            For i = 9 To 16
                If Range("J" & i).HasFormula Then
                    Range("J" & i).Value = 20
                    Range("L" & i).Value = 20
                End If
            Next i
        End If
        
        If Range("N5").Value >= Range("$B$2").Value And Range("N5").Value <= Range("$B$1").Value And Range("N5").HasFormula Then
            Range("N5").Value = 20
            Range("P5").Value = 20
            
            For i = 9 To 16
                If Range("N" & i).HasFormula Then
                    Range("N" & i).Value = 20
                    Range("P" & i).Value = 20
                End If
            Next i
        End If
        
        If Range("R5").Value >= Range("$B$2").Value And Range("R5").Value <= Range("$B$1").Value And Range("R5").HasFormula Then
            Range("R5").Value = 20
            Range("T5").Value = 20
            
            For i = 9 To 16
                If Range("R" & i).HasFormula Then
                    Range("R" & i).Value = 20
                    Range("T" & i).Value = 20
                End If
            Next i
        End If
    End If
    
    startTime = Range("X1").Value
    endTime = Range("Z1").Value
    
    If startTime < nowTime And nowTime <= endTime Then
        If Range("F5").Value <= Range("$B$3").Value And Range("H5").Value = 20 And Range("F5").HasFormula = False Then
            Range("F5").FormulaR1C1 = Range("F5").FormulaR1C1
            Range("H5").Value = 10
            
            For i = 9 To 16
                If Range("F" & i).HasFormula = False Then
                    Range("F" & i).FormulaR1C1 = Range("F" & i).FormulaR1C1
                    Range("H" & i).Value = 10
                End If
            Next i
        End If
        
    If Range("J5").Value <= Range("$B$3").Value And Range("L5").Value = 20 And Range("J5").HasFormula = False Then
            Range("J5").FormulaR1C1 = Range("J5").FormulaR1C1
            Range("L5").Value = 10
            
            For i = 9 To 16
                If Range("J" & i).HasFormula = False Then
                    Range("J" & i).FormulaR1C1 = Range("J" & i).FormulaR1C1
                    Range("L" & i).Value = 10
                End If
            Next i
        End If
If Range("N5").Value <= Range("$B$3").Value And Range("P5").Value = 20 And Range("N5").HasFormula = False Then
            Range("N5").FormulaR1C1 = Range("N5").FormulaR1C1
            Range("P5").Value = 10
            
            For i = 9 To 16
                If Range("N" & i).HasFormula = False Then
                    Range("N" & i).FormulaR1C1 = Range("N" & i).FormulaR1C1
                    Range("P" & i).Value = 10
                End If
            Next i
        End If
If Range("R5").Value <= Range("$B$3").Value And Range("T5").Value = 20 And Range("R5").HasFormula = False Then
            Range("R5").FormulaR1C1 = Range("R5").FormulaR1C1
            Range("T5").Value = 10
            
            For i = 9 To 16
                If Range("R" & i).HasFormula = False Then
                    Range("R" & i).FormulaR1C1 = Range("R" & i).FormulaR1C1
                    Range("T" & i).Value = 10
                End If
            Next i
        End If

 

[Skyybot]

What line are you getting the error at?

 

[hsandeep]

What line are you getting the error at?

I have tried to develop another code. In this there are some rather minor changes to the initial requirements (code is self-explanatory). I am unable to stop the Marching Ants. Someone please help

Sub CheckTime()
    Dim nowTime As Date
    Dim x1 As Date
    Dim z1 As Date
    Dim timeDiff As Long
    
    nowTime = Now()
    x1 = Range("X1").Value
    z1 = Range("Z1").Value
    
'Action 1

    If z1 <= nowTime Then
        'F5
        Range("F5").Copy
        Range("F5").PasteSpecial xlPasteValues
        Range("H5").Value = 10
        'F9:F16
        Range("F9:F16").Copy
        Range("F9:F16").PasteSpecial xlPasteValues
        Range("H9:H16").Value = 10
        'J5
        Range("J5").Copy
        Range("J5").PasteSpecial xlPasteValues
        Range("L5").Value = 10
        'J9:J16
        Range("J9:J16").Copy
        Range("J9:J16").PasteSpecial xlPasteValues
        Range("L9:L16").Value = 10
        'N5
        Range("N5").Copy
        Range("N5").PasteSpecial xlPasteValues
        Range("P5").Value = 10
        'N9:N16
        Range("N9:N16").Copy
        Range("N9:N16").PasteSpecial xlPasteValues
        Range("P9:P16").Value = 10
        'R5
        Range("R5").Copy
        Range("R5").PasteSpecial xlPasteValues
        Range("T5").Value = 10
        'R9:R16
        Range("R9:R16").Copy
        Range("R9:R16").PasteSpecial xlPasteValues
        Range("T9:T16").Value = 10
    End If
    
'Action 2

    If x1 < nowTime And nowTime <= z1 Then

'F5
        If Range("F5") <= Range("$B$3") And Range("H5") = 20 Then
            Range("F5").Copy
            Range("F5").PasteSpecial xlPasteValues
            Range("H5").Value = 10
'F9:F16
            Range("F9:F16").Copy
            Range("F9:F16").PasteSpecial xlPasteValues
            Range("H9:H16").Value = 10
        ElseIf Range("F5") <= Range("$B$3") And Range("H5") = 10 Then
            Range("F5").Copy
            Range("F5").PasteSpecial xlPasteValues
            Range("H5").Value = 20
            Range("F9:F16").Copy
            Range("F9:F16").PasteSpecial xlPasteValues
            Range("H9:H16").Value = 20
            
        ElseIf Range("F5") > Range("$B$3") And Range("H5") = 10 Then
            Range("H5").Value = 20
            Range("F5").Copy
            Range("F5").PasteSpecial xlPasteValues
            Range("F9:F16").PasteSpecial xlPasteFormula
            Range("F9:F16").Value = 10
        ElseIf Range("F5") > Range("$B$3") And Range("P5") = 10 Then
            Range("F5").Copy
            Range("F9:F16").PasteSpecial xlPasteFormula
            Range("F9:F16").Value = 10
            Range("H5").Value = 20
            Range("H9:H16").Value = 20
        End If

'J5
        If Range("J5") <= Range("$B$3") And Range("L5") = 20 Then
            Range("J5").Copy
            Range("J5").PasteSpecial xlPasteValues
            Range("L5").Value = 10
'J9:J16
            Range("J9:J16").Copy
            Range("J9:J16").PasteSpecial xlPasteValues
            Range("L9:L16").Value = 10
        ElseIf Range("J5") <= Range("$B$3") And Range("L5") = 10 Then
            Range("J5").Copy
            Range("J5").PasteSpecial xlPasteValues
            Range("L5").Value = 20
            Range("J9:J16").Copy
            Range("J9:J16").PasteSpecial xlPasteValues
            Range("L9:L16").Value = 20
            
        ElseIf Range("J5") > Range("$B$3") And Range("L5") = 10 Then
            Range("L5").Value = 20
            Range("J5").Copy
            Range("J5").PasteSpecial xlPasteValues
            Range("J9:J16").PasteSpecial xlPasteFormula
            Range("J9:J16").Value = 10
        ElseIf Range("J5") > Range("$B$3") And Range("L5") = 10 Then
            Range("J5").Copy
            Range("J9:J16").PasteSpecial xlPasteFormula
            Range("J9:J16").Value = 10
            Range("L5").Value = 20
            Range("L9:L16").Value = 20
        End If

'N5
        If Range("N5") <= Range("$B$3") And Range("P5") = 20 Then
            Range("N5").Copy
            Range("N5").PasteSpecial xlPasteValues
            Range("P5").Value = 10
'N9:N16
            Range("N9:N16").Copy
            Range("N9:N16").PasteSpecial xlPasteValues
            Range("P9:P16").Value = 10
        ElseIf Range("N5") <= Range("$B$3") And Range("P5") = 10 Then
            Range("N5").Copy
            Range("N5").PasteSpecial xlPasteValues
            Range("P5").Value = 20
            Range("N9:N16").Copy
            Range("N9:N16").PasteSpecial xlPasteValues
            Range("P9:P16").Value = 20
            
        ElseIf Range("N5") > Range("$B$3") And Range("P5") = 10 Then
            Range("P5").Value = 20
            Range("N5").Copy
            Range("N5").PasteSpecial xlPasteValues
            Range("N9:N16").PasteSpecial xlPasteFormula
            Range("N9:N16").Value = 10
        ElseIf Range("N5") > Range("$B$3") And Range("P5") = 10 Then
            Range("N5").Copy
            Range("N9:N16").PasteSpecial xlPasteFormula
            Range("N9:N16").Value = 10
            Range("P5").Value = 20
            Range("P9:P16").Value = 20
        End If
        
'R5
        If Range("R5") <= Range("$B$3") And Range("T5") = 20 Then
            Range("R5").Copy
            Range("R5").PasteSpecial xlPasteValues
            Range("T5").Value = 10
'R9:R16
            Range("R9:R16").Copy
            Range("R9:R16").PasteSpecial xlPasteValues
            Range("T9:T16").Value = 10
        ElseIf Range("R5") <= Range("$B$3") And Range("T5") = 10 Then
            Range("R5").Copy
            Range("R5").PasteSpecial xlPasteValues
            Range("T5").Value = 20
            Range("R9:R16").Copy
            Range("R9:R16").PasteSpecial xlPasteValues
            Range("T9:T16").Value = 20
            
        ElseIf Range("R5") > Range("$B$3") And Range("T5") = 10 Then
            Range("T5").Value = 20
            Range("R5").Copy
            Range("R5").PasteSpecial xlPasteValues
            Range("R9:R16").PasteSpecial xlPasteFormula
            Range("R9:R16").Value = 10
        ElseIf Range("R5") > Range("$B$3") And Range("T5") = 10 Then
            Range("R5").Copy
            Range("R9:R16").PasteSpecial xlPasteFormula
            Range("R9:R16").Value = 10
            Range("T5").Value = 20
            Range("T9:T16").Value = 20
        End If
 
            
End If
Application.CutCopyMode = True

End Sub

 

[MARK858]

). I am unable to stop the Marching Ants. Someone please help

Application.CutCopyMode = True should be = False

 

[hsandeep]

Thanks MARK858 for your time and help

 

[MARK858]

You're welcome

 

[hsandeep]

You're welcome

I am using below code (self explanatory)

Sub UpdateCells()
   
    ' Define variables
    Dim x1 As Date
    Dim z1 As Date
    Dim b1 As Double
    Dim b2 As Double
    Dim b3 As Double
    Dim hRange As Range
    Dim lRange As Range
    Dim pRange As Range
    Dim tRange As Range
   
    ' Assign values to variables
    x1 = Range("X1").Value
    z1 = Range("Z1").Value
    b1 = Range("B1").Value
    b2 = Range("B2").Value
   
    b3 = Range("B3").Value
   
    Set hRange = Range("H5,H9:H16")
    Set lRange = Range("L5,L9:L16")
    Set pRange = Range("P5,P9:P16")
    Set tRange = Range("T5,T9:T16")
   
    ' Check if Z1 is less than or equal to current date and time
If z1 <= Now() Then
        ' Insert formula back into cells F5, J5, N5, R5, F9:F16, J9:J16, N9:N16, and R9:R16
        Range("F5,J5,N5,R5,F9:F16,J9:J16,N9:N16,R9:R16").FormulaR1C1 = "=[RC1]"
        ' Fill cells H5, L5, P5, T5, H9:H16, L9:L16, P9:P16, and T9:T16 with the value 10
        hRange.Value = 10
        lRange.Value = 10
        pRange.Value = 10
        tRange.Value = 10
End If
   
    ' Check if current date and time is greater than X1 and less than or equal to Z1
If x1 < Now() And Now() <= z1 Then
    ' Check if cells F5, J5, N5, and R5 are between B3 and B1 and H5, L5, P5, and T5 = 10
   
        If Range("F5").Value > b3 And Range("F5").Value < b1 And Range("H5").Value = 10 Then
            ' Remove formula from cells F5, F9:F16 and fill H5, H9:H16 with value 20
            Dim f As Range
            Set f = Union(Range("F5"), Range("F9:F16"))
                If f.HasFormula Then
                    f.Copy
                    f.PasteSpecial xlPasteValues
                    hRange.Value = 20
               
                End If
        End If
End If

If x1 < Now() And Now() <= z1 Then
        If Range("J5").Value > b3 And Range("J5").Value < b1 And Range("L5").Value = 10 Then
            ' Remove formula from cells J5, J9:J16 and fill L5, L9:L16 with value 20
            Dim j As Range
            Set j = Union(Range("J5"), Range("J9:J16"))
                If j.HasFormula Then
                    j.Copy
                    j.PasteSpecial xlPasteValues
                    lRange.Value = 20
                 End If
        End If
End If

If x1 < Now() And Now() <= z1 Then
        If Range("N5").Value > b3 And Range("N5").Value < b1 And Range("P5").Value = 10 Then
            ' Remove formula from cells N5, N9:N16 and fill P5, P9:P16 with value 20
            Dim n As Range
            Set n = Union(Range("N5"), Range("N9:N16"))
                If n.HasFormula Then
                    n.Copy
                    n.PasteSpecial xlPasteValues
                    pRange.Value = 20
                End If
        End If
End If

If x1 < Now() And Now() <= z1 Then
    If Range("R5").Value > b3 And Range("R5").Value < b1 And Range("T5").Value = 10 Then
            ' Remove formula from cells R5, R9:R16 and fill T5, T9:T16 with value 20
            Dim r As Range
            Set r = Union(Range("R5"), Range("R9:R16"))
                If r.HasFormula Then
                    r.Copy
                    r.PasteSpecial xlPasteValues
                    tRange.Value = 20
                End If
    End If
End If
   
    ' Check if current date and time is greater than X1 and less than or equal to Z1
If x1 < Now() And Now() <= z1 Then
    ' Check if cells F5, J5, N5, and R5 are between B3 and B2 and H5, L5, P5, and T5 = 10
    If Range("F5").Value <= b3 And Range("F5").Value > b2 And Range("H5").Value = 10 Then
            ' Remove formula from cells F5, F9:F16 and fill H5, H9:H16 with value 20
            Dim ff As Range
            Set ff = Union(Range("F5"), Range("F9:F16"))
                If ff.HasFormula Then
                    ff.Copy
                    ff.PasteSpecial xlPasteValues
                    hRange.Value = 20
                End If
    End If
End If

If x1 < Now() And Now() <= z1 Then
    If Range("J5").Value <= b3 And Range("J5").Value > b2 And Range("L5").Value = 10 Then
        ' Remove formula from cells J5, J9:J16 and fill L5, L9:L16 with value 20
        Dim jj As Range
        Set jj = Union(Range("J5"), Range("J9:J16"))
        jj.NumberFormat = "General"
        If Not jj Is Nothing And Not IsEmpty(jj) Then
            Debug.Print jj.Address, jj.Value, jj.HasFormula
            Debug.Print jj.Address, jj.NumberFormat
            If jj.HasFormula Then
                jj.Copy
                jj.PasteSpecial xlPasteValuesAndNumberFormats
               
                lRange.Value = 20
            End If
        End If
    End If
End If


If x1 < Now() And Now() <= z1 Then
    If Range("N5").Value <= b3 And Range("N5").Value > b2 And Range("P5").Value = 10 Then
            ' Remove formula from cells N5, N9:N16 and fill P5, P9:P16 with value 20
            Dim nn As Range
            Set nn = Union(Range("N5"), Range("N9:N16"))
                If nn.HasFormula Then
                    nn.Copy
                    nn.PasteSpecial xlPasteValues
                    pRange.Value = 20
                End If
    End If
End If


If x1 < Now() And Now() <= z1 Then
    If Range("R5").Value <= b3 And Range("R5").Value > b2 And Range("T5").Value = 10 Then
            ' Remove formula from cells R5, R9:R16 and fill T5, T9:T16 with value 20
            Dim rr As Range
            Set rr = Union(Range("R5"), Range("R9:R16"))
                If rr.HasFormula Then
                    rr.Copy
                    rr.PasteSpecial xlPasteValues
                    tRange.Value = 20
                End If
    End If
End If

     ' Check if current date and time is greater than X1 and less than or equal to Z1
If x1 < Now() And Now() <= z1 Then
     ' Check if cells F5, J5, N5, and R5 are less than B3 and H5, L5, P5, and T5 = 20
            If Range("F5").Value <= b3 And Range("H5").Value = 20 Then
     ' Insert formula back into cells F5, F9:F16 and fill H5, H9:H16 with value 10
                Range("F5,J5,N5,R5,F9:F16,J9:J16,N9:N16,R9:R16").FormulaR1C1 = "=[RC1]"
                hRange.Value = 10
                lRange.Value = 10
                pRange.Value = 10
                tRange.Value = 10
            End If
End If
 

End Sub

After the last code line in the 'code lines'

If x1 < Now() And Now() <= z1 Then
    If Range("J5").Value <= b3 And Range("J5").Value > b2 And Range("L5").Value = 10 Then
        ' Remove formula from cells J5, J9:J16 and fill L5, L9:L16 with value 20
        Dim jj As Range
        Set jj = Union(Range("J5"), Range("J9:J16"))
        jj.NumberFormat = "General"
        If Not jj Is Nothing And Not IsEmpty(jj) Then
            Debug.Print jj.Address, jj.Value, jj.HasFormula
            Debug.Print jj.Address, jj.NumberFormat
            If jj.HasFormula Then
                jj.Copy
                jj.PasteSpecial xlPasteValuesAndNumberFormats

I get Run-time error 1004 & the yellow color background bar is on the code line (when F8 is pressed while debugging)
jj.PasteSpecial xlPasteValuesAndNumberFormats

Someone help.

 

[hsandeep]

The latest code is

Sub MySub()

Dim x1 As Date, z1 As Date, nowtime As Date: nowtime = Now()
    x1 = Range("X1").Value: z1 = Range("Z1").Value

Dim hRange As Range, lRange As Range, pRange As Range, tRange As Range
    Set hRange = Union(Range("H5"), Range("H9:H16")): Set lRange = Union(Range("L5"), Range("L9:L16")): Set pRange = Union(Range("P5"), Range("P9:P16")): Set tRange = Union(Range("T5"), Range("T9:T16"))

Dim hasFormula As Boolean: Dim cell As Range

            If Not IsDate(x1) Or Not IsDate(z1) Or Not IsNumeric(b1) Or Not IsNumeric(b2) Or Not IsNumeric(b3) Then Exit Sub
 
'Action 1
                    If z1 <= nowtime Then
                        Set cell = Range("F5")
                        cell.FormulaR1C1 = "=RC1"
                        Set cell = Range("F9:F16")
                        cell.FormulaR1C1 = "=RC1"
                            If Range("H5").Value <> 10 Then
                                For Each cell In hRange: hRange.Value = 10: Next cell
                            End If
                           
                        Set cell = Range("J5")
                        cell.FormulaR1C1 = "=RC1"
                        Set cell = Range("J9:J16")
                        cell.FormulaR1C1 = "=RC1"
                            If Range("L5").Value <> 10 Then
                                For Each cell In lRange: lRange.Value = 10: Next cell
                            End If
                           
                        Set cell = Range("N5")
                        cell.FormulaR1C1 = "=RC1"
                        Set cell = Range("N9:N16")
                        cell.FormulaR1C1 = "=RC1"
                            If Range("P5").Value <> 10 Then
                                For Each cell In pRange: pRange.Value = 10: Next cell
                            End If
                           
                        Set cell = Range("R5")
                        cell.FormulaR1C1 = "=RC1"
                        Set cell = Range("R9:R16")
                        cell.FormulaR1C1 = "=RC1"
                            If Range("T5").Value <> 10 Then
                                For Each cell In tRange: tRange.Value = 10: Next cell
                            End If

                    End If

'Action 2
                    If x1 < nowtime And nowtime <= z1 Then
                        If Range("F5").Value > b3 And Range("F5").Value < b1 And Range("H5").Value = 10 Then
                            If Range("F5").hasFormula Then
                                    With Range("F5")
                                        .Value = .Value
                                    End With
                                    With Range("F9:F16")
                                        .Value = .Value
                                    End With
                            End If
                        End If
                                For Each cell In hRange: hRange.Value = 20: Next cell
                        End If
                       
                    If x1 < nowtime And nowtime <= z1 Then
                        If Range("J5").Value > b3 And Range("J5").Value < b1 And Range("L5").Value = 10 Then
                            If Range("J5").hasFormula Then
                                    With Range("J5")
                                        .Value = .Value
                                    End With
                                    With Range("J9:J16")
                                        .Value = .Value
                                    End With
                            End If
                        End If
                                For Each cell In lRange: lRange.Value = 20: Next cell
                        End If
                       
                    If x1 < nowtime And nowtime <= z1 Then
                        If Range("N5").Value > b3 And Range("N5").Value < b1 And Range("P5").Value = 10 Then
                            If Range("N5").hasFormula Then
                                    With Range("N5")
                                        .Value = .Value
                                    End With
                                    With Range("N9:N16")
                                        .Value = .Value
                                    End With
                            End If
                        End If
                                For Each cell In pRange: pRange.Value = 20: Next cell
                        End If
                       
                    If x1 < nowtime And nowtime <= z1 Then
                        If Range("R5").Value > b3 And Range("R5").Value < b1 And Range("T5").Value = 10 Then
                            If Range("R5").hasFormula Then
                                    With Range("R5")
                                        .Value = .Value
                                    End With
                                    With Range("R9:R16")
                                        .Value = .Value
                                    End With
                            End If
                        End If
                                For Each cell In tRange: tRange.Value = 20: Next cell
                        End If
                       
                   
                   
                   
                   
'Action 3
                    If x1 < nowtime And nowtime <= z1 Then
                        If Range("F5").Value <= b3 And Range("F5").Value > b2 And Range("H5").Value = 10 Then
                            If Range("F5").hasFormula Then
                                    With Range("F5")
                                        .Value = .Value
                                    End With
                                    With Range("F9:F16")
                                        .Value = .Value
                                    End With
                            End If
                                For Each cell In hRange: hRange.Value = 20: Next cell
                        End If
                    End If
                       
                    If x1 < nowtime And nowtime <= z1 Then
                        If Range("J5").Value <= b3 And Range("J5").Value > b2 And Range("L5").Value = 10 Then
                            If Range("J5").hasFormula Then
                                    With Range("J5")
                                        .Value = .Value
                                    End With
                                    With Range("J9:J16")
                                        .Value = .Value
                                    End With
                            End If
                                For Each cell In lRange: lRange.Value = 20: Next cell
                        End If
                    End If
                       
                    If x1 < nowtime And nowtime <= z1 Then
                        If Range("N5").Value <= b3 And Range("N5").Value > b2 And Range("P5").Value = 10 Then
                            If Range("N5").hasFormula Then
                                    With Range("N5")
                                        .Value = .Value
                                    End With
                                    With Range("N9:N16")
                                        .Value = .Value
                                    End With
                            End If
                                For Each cell In pRange: pRange.Value = 20: Next cell
                        End If
                    End If
                       
                    If x1 < nowtime And nowtime <= z1 Then
                        If Range("R5").Value <= b3 And Range("R5").Value > b2 And Range("T5").Value = 10 Then
                            If Range("R5").hasFormula Then
                                    With Range("R5")
                                        .Value = .Value
                                    End With
                                    With Range("R9:R16")
                                        .Value = .Value
                                    End With
                            End If
                                For Each cell In tRange: tRange.Value = 20: Next cell
                        End If
                    End If
                       
                       
                   
 'Action 4
                    If x1 < Now() And Now() <= z1 Then
                        If Range("F5").Value <= b3 And Range("H5").Value = 20 Then
                            If Not Range("F5").hasFormula Then Range("F5,F9:F16").FormulaR1C1 = "=[RC1]"
                            End If
                                For Each cell In hRange: hRange.Value = 10: Next cell
                        End If
           
            
                   
                   
                    If x1 < Now() And Now() <= z1 Then
                        If Range("J5").Value <= b3 And Range("L5").Value = 20 Then
                            If Not Range("J5").hasFormula Then Range("J5,J9:J16").FormulaR1C1 = "=[RC1]"
                            End If
                                For Each cell In lRange: lRange.Value = 10: Next cell
                        End If
                   
                   
                   
                    If x1 < Now() And Now() <= z1 Then
                        If Range("N5").Value <= b3 And Range("O5").Value = 20 Then
                            If Not Range("N5").hasFormula Then Range("N5,N9:N16").FormulaR1C1 = "=[RC1]"
                            End If
                                For Each cell In pRange: pRange.Value = 10: Next cell
                        End If
            
                   
                   
                    If x1 < Now() And Now() <= z1 Then
                        If Range("R5").Value <= b3 And Range("T5").Value = 20 Then
                            If Not Range("R5").hasFormula Then Range("R5,R9:R16").FormulaR1C1 = "=[RC1]"
                            End If
                                For Each cell In tRange: tRange.Value = 10: Next cell
                        End If
             
                         
End Sub

Below is a brief summary of the actions taken in the code:
Action 1:

• If the value in cell Z1 is less than or equal to the current date and time (Now()), the following actions are taken:
• The values of cells F5 and F9:F16 are set to their own values using a formula (to fix the value)
• If the value of cell H5 is not 10, the values of cells H9:H16 are set to 10.

Action 2:

• If the value in cell X1 is less than the current date and time (Now()) and the value in cell Z1 is greater than or equal to the current date and time (Now()), the following actions are taken:
• If the value of cell F5 is greater than the value in cell B3 and less than the value in cell B1, and the value of cell H5 is 10, the following actions are taken:
• The values of cells F5 and F9:F16 are set to their own values (to fix the value).
• The values of cells H9:H16 are set to 20.

Action 3:

• If the value in cell X1 is less than the current date and time (Now()) and the value in cell Z1 is greater than or equal to the current date and time (Now()), the following actions are taken:
• If the value of cell F5 is less than or equal to the value in cell B3 and greater than the value in cell B2, and the value of cell H5 is 10, the following actions are taken:
• The values of cells F5 and F9:F16 are set to their own values (to fix the value).
• The values of cells H9:H16 are set to 20.

Action 4:

• Similar to Action 2, but for cells J5, N5, and R5 and their respective columns of cells. The code also checks that certain cells have the expected data types and exits the subroutine if any of them do not.

The mistake is in Action 4. F5 should keep the formula "=G5" but it changes to "=$A5". Similarly in F9:F16 it should have kept "=G9":"=G16" but it changes to "=$A9":"=$A16"

 

[MARK858]

Try RC[1] not RC1

 

[hsandeep]

Try RC[1] not RC1

What a silly stupidity I have done & was wasting my time. Thanks MARK858, it should be =RC[1]. Can you help me how to use the Offset at this point of code to perform the same action.