'Добавление и замена условного форматирования обычным

Sub DeleteUFD()

Dim rRange As Range

With ThisWorkbook.Sheets("Bos")

iNbCln = .Cells(4, 256).End(xlToLeft).Column

iNbRow = .Columns(1).Rows(Rows.Count).End(xlUp).Row

For i = 4 To iNbRow

For j = 2 To iNbCln

Set rRange = .Cells(i, j)

LColor = rRange.Cells.DisplayFormat.Interior.Color

rRange.Interior.Color = LColor

Next j

Next i

.Cells.FormatConditions.Delete

End With

' Set rRange = ActiveCell

' LColor = rRange.Cells.DisplayFormat.Interior.Color

'' - : DisplayFormat : : DisplayFormat/DisplayFormat

'Range("AP1").Interior.Color = LColor

End Sub

Sub Условное_Форматирование()

Dim strRange As String

Dim rRange As Range

With ThisWorkbook.Sheets("Bos")

i = 5

Do While .Cells(i, 1) <> ""

i = i + 1

Loop

iNbRow1 = i + 3 '2 '.Columns(1).Rows(Rows.Count).End(xlUp).Row

iNbRow2 = .Columns(1).Rows(Rows.Count).End(xlUp).Row

iNbCol = .Cells(4, Columns.Count).End(xlToLeft).Column

' strRange = "$C$12:$CC$" & iNbRow

' Set rRange = Range(strRange)

Set rRange = Range(.Cells(iNbRow1, 4), .Cells(iNbRow2, iNbCol))

End With

ThisWorkbook.Sheets("Bos").Cells.FormatConditions.Delete

With rRange

.FormatConditions.Delete

'зеленый

.FormatConditions.Add Type:=xlExpression, Formula1:= _

"=ЕСЛИ(C" & iNbRow1 & ">=D" & iNbRow1 & ";1;0)"

'"=ЕСЛИ(C12>=D12;1;0)"

.FormatConditions(.FormatConditions.Count).SetFirstPriority

With .FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 13434777 '5287936

.TintAndShade = 0

End With

.FormatConditions(1).StopIfTrue = False

'красный

.FormatConditions(1).StopIfTrue = False

.FormatConditions.Add Type:=xlExpression, Formula1:= _

"=ЕСЛИ(C" & iNbRow1 & "<D" & iNbRow1 & ";1;0)"

.FormatConditions(.FormatConditions.Count).SetFirstPriority

With .FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 8420607 '255

.TintAndShade = 0

End With

.FormatConditions(1).StopIfTrue = False

'' 'белый

'' .FormatConditions.Add Type:=xlExpression, Formula1:= _

'' "=ЕСЛИ(B12=0;1;0)"

'' .FormatConditions(.FormatConditions.Count).SetFirstPriority

'' With .FormatConditions(1).Interior

'' .PatternColorIndex = xlAutomatic

'' .ThemeColor = xlThemeColorDark1

'' .TintAndShade = 0

'' End With

'' .FormatConditions(1).StopIfTrue = False

'белый2

.FormatConditions.Add Type:=xlExpression, Formula1:= _

"=ЕСЛИ(D$" & iNbRow1 - 1 & "=0;1;0)"

'"=ЕСЛИ(ИЛИ(B$4="""";C$4="""";C$5=0);1;0)"

.FormatConditions(.FormatConditions.Count).SetFirstPriority

With .FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorDark1

.TintAndShade = 0

End With

.FormatConditions(1).StopIfTrue = False

End With

'удалить условное форматирование

DeleteUFD

End Sub