haudrauf64
02-17-2022, 10:01 AM
Hi there!
Sorry in advance for being stupid :-) first time working with VBA (and this forum)...
Goal: Some cells in B1:E1 (TC-0110 = main hazard) may differ to cells in F2:I2 (subhazard to TC-0110). I'd like to build a macro that marks these differences. It should be possible to select an orange cell and the macro does it's job in marking the differences in it's related subhazards.
29416
Question: I do not get how to apply a NOT-function (or something related) to compare the ranges but in relation to the ActiveCell. As the macro iterates trough the rows below the orange field, it should apply the conditional formatting to where it is at the moment (for example =NOT($B1=F3) when it is relating to TC-0112)
This is what I tried:
Sub ChangeMarker()
Dim Mainhazard As String
Mainhazard = ActiveCell.Value
Mainhazard = Right(ActiveCell.Value, 3)
Dim Subhazard As String
Subhazard = ActiveCell.Offset(i, 0).Value
Subhazard = Right(ActiveCell.Offset(i, 0).Value, 3)
'This should activate the Loop?
For i = 1 To Rows.Count
Next i
'Checks if Subhazard is belonging to the Mainhazard
If Mainhazard - Subhazard < 10 Then
'Select the range where the conditional formatting should apply (--> but does not work with i)
'Range("F2:I2").Select
'Range("B1:E1").Offset(1, 4).Select
Range(ActiveCell.Offset(1, 5), ActiveCell.Offset(1, 8)).Select
'This compares the hazard indicators of the mainhazard (ex. B1:E1) to the hazard indicators of the subhazard (ex.F2:I2)
'Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NOT(B$1=F2)"
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NOT(ActiveCell.Offset(0, 1).Range(ActiveCell, ActiveCell.Offset(0, 3))=ActiveCell.Offset(1,5).Range(ActiveCell, ActiveCell.Offset(0,3))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End If
End Sub
Thank you so much for any advide. I'm stuck here for ages!:banghead:
Greetz
Sorry in advance for being stupid :-) first time working with VBA (and this forum)...
Goal: Some cells in B1:E1 (TC-0110 = main hazard) may differ to cells in F2:I2 (subhazard to TC-0110). I'd like to build a macro that marks these differences. It should be possible to select an orange cell and the macro does it's job in marking the differences in it's related subhazards.
29416
Question: I do not get how to apply a NOT-function (or something related) to compare the ranges but in relation to the ActiveCell. As the macro iterates trough the rows below the orange field, it should apply the conditional formatting to where it is at the moment (for example =NOT($B1=F3) when it is relating to TC-0112)
This is what I tried:
Sub ChangeMarker()
Dim Mainhazard As String
Mainhazard = ActiveCell.Value
Mainhazard = Right(ActiveCell.Value, 3)
Dim Subhazard As String
Subhazard = ActiveCell.Offset(i, 0).Value
Subhazard = Right(ActiveCell.Offset(i, 0).Value, 3)
'This should activate the Loop?
For i = 1 To Rows.Count
Next i
'Checks if Subhazard is belonging to the Mainhazard
If Mainhazard - Subhazard < 10 Then
'Select the range where the conditional formatting should apply (--> but does not work with i)
'Range("F2:I2").Select
'Range("B1:E1").Offset(1, 4).Select
Range(ActiveCell.Offset(1, 5), ActiveCell.Offset(1, 8)).Select
'This compares the hazard indicators of the mainhazard (ex. B1:E1) to the hazard indicators of the subhazard (ex.F2:I2)
'Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NOT(B$1=F2)"
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NOT(ActiveCell.Offset(0, 1).Range(ActiveCell, ActiveCell.Offset(0, 3))=ActiveCell.Offset(1,5).Range(ActiveCell, ActiveCell.Offset(0,3))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End If
End Sub
Thank you so much for any advide. I'm stuck here for ages!:banghead:
Greetz