Conditional Formatting with Macro

[ofd2008]

Hi,
l want to do conditional formatting rule with macro for multiple sequences.
Example

Formula=EĞER($S$25=0;1)    Target Practice=$C$25:$N$47
Formula=EĞER($S$26=0;1)    Target Practice=$C$48:$N$70
Formula=EĞER($S$27=0;1)    Target Practice=$C$71:$N$93
Formula=EĞER($S$28=0;1)    Target Practice=$C$94:$N$116
...
...
Formula=EĞER($S$124=0;1)   Target Practice=$C$2302:$N$2324

If "S25" is equal to "0" or blank cells; C25: N47 in the block, text and background (fill) "white" color, borders the "No" I want to do.
If "S25" is not equal to "0" or full, C25: N47 in the block will return back.

I tried to write the code but did not.

Sub Makro1()
'
' Makro1 Makro
'

'
    Range("C25:N47").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=EĞER($S$25=0;1)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).Borders(xlLeft).LineStyle = xlNone
    Selection.FormatConditions(1).Borders(xlRight).LineStyle = xlNone
    Selection.FormatConditions(1).Borders(xlTop).LineStyle = xlNone
    Selection.FormatConditions(1).Borders(xlBottom).LineStyle = xlNone
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
    ActiveWindow.SmallScroll Down:=18
    Range("C48:N70").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=EĞER($S$26=0;1)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).Borders(xlLeft).LineStyle = xlNone
    Selection.FormatConditions(1).Borders(xlRight).LineStyle = xlNone
    Selection.FormatConditions(1).Borders(xlTop).LineStyle = xlNone
    Selection.FormatConditions(1).Borders(xlBottom).LineStyle = xlNone
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
    ActiveWindow.SmallScroll Down:=15
    Range("C71:N93").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=EĞER($S$27=0;1)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).Borders(xlLeft).LineStyle = xlNone
    Selection.FormatConditions(1).Borders(xlRight).LineStyle = xlNone
    Selection.FormatConditions(1).Borders(xlTop).LineStyle = xlNone
    Selection.FormatConditions(1).Borders(xlBottom).LineStyle = xlNone
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -4.99893185216834E-02
    End With
    Selection.FormatConditions(1).StopIfTrue = True
End Sub

or

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tr, ilk, son As Integer
tr = Target.Row - 24
ilk = tr * 23 + 2
son = ilk + 22
If Target.Column = 19 Then
If Target.Value = "" Or Target.Value = 0 Then
' If the cell is empty 
Range("C" & ilk & ":N" & son).Borders(xlLeft).LineStyle = xlNone
Range("C" & ilk & ":N" & son).Borders(xlRight).LineStyle = xlNone
Range("C" & ilk & ":N" & son).Borders(xlTop).LineStyle = xlNone
Range("C" & ilk & ":N" & son).Borders(xlBottom).LineStyle = xlNone
Else
'If the cell is full
Range("C" & ilk & ":N" & son).Borders(xlLeft).LineStyle = xlAutomatic
Range("C" & ilk & ":N" & son).Borders(xlRight).LineStyle = xlAutomatic
Range("C" & ilk & ":N" & son).Borders(xlTop).LineStyle = xlAutomatic
Range("C" & ilk & ":N" & son).Borders(xlBottom).LineStyle = xlAutomatic
End If
End If
End Sub

Sorry my bad English.
Thanks for your helping.

[AlphaFrog]

Try this...

Sub Makro1()
    Dim i As Long
    
    With Range("C25:N2324")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$S$25=0"
        With .FormatConditions(1)
            .Interior.ColorIndex = 2    'white
            .Font.ColorIndex = 2
            .Borders.ColorIndex = 2
        End With
    End With
    
    For i = 1 To 99
        Range("C25:N47").Offset(i * 23).FormatConditions(1).Modify _
            Type:=xlExpression, Formula1:="=$S$" & i + 25 & "=0"
    Next i
    
End Sub

[ofd2008]

Hi, AlphaFrog
Code is working properly. There is only one mistake.
This Code:
Formula1:="=$S$" & i + 25 & "=0"

"S26" and the others working good but "S25" is not working properly for i=1 to 99.

[AlphaFrog]

I don't understand what that means?

[ofd2008]

    For i = 1 To 99
        Range("C25:N47").Offset(i * 23).FormatConditions(1).Modify _
            Type:=xlExpression, Formula1:="=$S$" & i + 25 & "=0"
    Next i

When l enter a value in "S25", it is not working. But the other "S26,S27,S28......" is working properly. (May be "i" starting from 1 in this code (Formula1:="=$S$" & i + 25 & "=0").)

[AlphaFrog]

    For i = 1 To 99
        Range("C25:N47").Offset(i * 23).FormatConditions(1).Modify _
            Type:=xlExpression, Formula1:="=$S$" & i + 25 & "=0"
    Next i

When l enter a value in "S25", it is not working. But the other "S26,S27,S28......" is working properly. (May be "i" starting from 1 in this code (Formula1:="=$S$" & i + 25 & "=0").)

Entering any value in S25 works for me on your example file. Perhaps you put a space character in S25 ?

Look at the conditional format (CF) formula for any cell in C25:N47, it should be =$S$25=0

The With-End With code block sets all the CF formulas to S25 and the format for the entire range C25:N2324. The For-Next loop then just modifies the CF formulas for the ranges below C25:N47.

[ofd2008]

Hi, AlphaFrog
The problem was solved.

Working Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
Application.ScreenUpdating = False
    With Range("C25:N2324")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$S$25=0"
        With .FormatConditions(1)
            .Interior.ColorIndex = 2    'white
            .Font.ColorIndex = 2
            .Borders.ColorIndex = 2
        End With
    End With
    For i = 0 To 99
        Range("C25:N47").Offset(i * 23).FormatConditions(1).Modify _
            Type:=xlExpression, Formula1:="=$S$" & i + 25 & "=0"
    Next i
Application.ScreenUpdating = True
End Sub

And The other macro with commandclick button. (*** I could not do macro when button pressed with the above code. Therefore a long macro appeared.)

Private Sub CommandButton1_Click()
    Dim i As Long
   
Application.ScreenUpdating = False
    For i = 0 To 99
    'MsgBox Range("C25:N47").Offset(i * 23).Address
    If Range("S" & i + 25).Value = "" Then
        Range("C25:N47").Offset(i * 23).Interior.Color = vbWhite
        Range("C25:N47").Offset(i * 23).Font.Color = vbWhite
        Range("C25:N47").Offset(i * 23).Borders.Color = vbWhite
        Else
        Range("E25:N46").Offset(i * 23).Interior.Color = vbWhite
        Range("E25:N46").Offset(i * 23).Font.Color = vbBlack
        Range("E25:N46").Offset(i * 23).Borders.Color = vbBlack
        Range("E47:J47").Offset(i * 23).Interior.Color = vbWhite
        Range("E47:J47").Offset(i * 23).Font.Color = vbBlack
        Range("E47:J47").Offset(i * 23).Borders.Color = vbBlack
        Range("N47").Offset(i * 23).Interior.Color = vbWhite
        Range("N47").Offset(i * 23).Font.Color = vbBlack
        Range("N47").Offset(i * 23).Borders.Color = vbBlack
        Range("C25:D47").Offset(i * 23).Interior.ColorIndex = 22
        Range("C25:D47").Offset(i * 23).Font.Color = vbBlack
        Range("C25:D47").Offset(i * 23).Borders.Color = vbBlack
        Range("L47:M47").Offset(i * 23).Interior.ColorIndex = 22
        Range("L47:M47").Offset(i * 23).Font.Color = vbBlack
        Range("L47:M47").Offset(i * 23).Borders.Color = vbBlack
        End If
    Next i
Application.ScreenUpdating = True
End Sub