L
Lise
Hi
New to VBA so apologies in advance if a dumb question!
I have managed to get to the following which is working well but I now want
corresponding cells in the "k" column to change to specific colours based on
the data in "I" and "J" columns and seem to have put myself in a corner -
what os the best way to write such a request please?
'Convert to FMECA format, and set matrix row
If consequence = "A - Almost Certain" Or LCase(consequence) = "a" Or
LCase(consequence) = "a" Or LCase(consequence) = "almost certain" Then
Range("i" & iRow).Value = "A - Almost Certain"
iconsequence = 1
ElseIf consequence = "B - Likely" Or LCase(consequence) = "b" Or
LCase(consequence) = "l" Or LCase(consequence) = "likely" Then
Range("i" & iRow).Value = "B - Likely"
iconsequence = 2
ElseIf consequence = "C - Possible" Or LCase(consequence) = "c" Or
LCase(consequence) = "p" Or LCase(consequence) = "possible" Then
Range("i" & iRow).Value = "C - Possible"
iconsequence = 3
ElseIf consequence = "D - Unlikely" Or LCase(consequence) = "d" Or
LCase(consequence) = "u" Or LCase(consequence) = "unlikely" Then
Range("i" & iRow).Value = "D - Unlikely"
iconsequence = 4
ElseIf consequence = "E - Rare" Or LCase(consequence) = "e" Or
LCase(consequence) = "r" Or LCase(consequence) = "rare" Then
Range("i" & iRow).Value = "E - Rare"
iconsequence = 5
'Convert to FMECA format, and set matrix column
End If
If likelihood = "5 - Catastrophic" Or likelihood = "5" Or
LCase(likelihood) = "ca" Or LCase(likelihood) = "5" Or LCase(likelihood) =
"catastrophic" Then
Range("j" & iRow).Value = "5 - Catastrophic"
ilikelihood = 5
ElseIf likelihood = "4 - Major" Or likelihood = "4" Or LCase(likelihood)
= "ma" Or LCase(likelihood) = "4" Or LCase(likelihood) = "major" Then
Range("j" & iRow).Value = "4 - Major"
ilikelihood = 4
ElseIf likelihood = "3 - Moderate" Or likelihood = "3" Or
LCase(likelihood) = "mo" Or LCase(likelihood) = "3" Or LCase(likelihood) =
"moderate" Then
Range("j" & iRow).Value = "3 - Moderate"
ilikelihood = 3
ElseIf likelihood = "2 - Minor" Or likelihood = "2" Or LCase(likelihood)
= "mi" Or LCase(likelihood) = "2" Or LCase(likelihood) = "minor" Then
Range("j" & iRow).Value = "2 - Moderate"
ilikelihood = 2
ElseIf likelihood = "1 - Insignificant" Or likelihood = "1" Or
LCase(likelihood) = "in" Or LCase(likelihood) = "1" Or LCase(likelihood) =
"insignificant" Then
Range("j" & iRow).Value = "1 - Insignificant"
ilikelihood = 1
Else
ilikelihood = "0"
New to VBA so apologies in advance if a dumb question!
I have managed to get to the following which is working well but I now want
corresponding cells in the "k" column to change to specific colours based on
the data in "I" and "J" columns and seem to have put myself in a corner -
what os the best way to write such a request please?
'Convert to FMECA format, and set matrix row
If consequence = "A - Almost Certain" Or LCase(consequence) = "a" Or
LCase(consequence) = "a" Or LCase(consequence) = "almost certain" Then
Range("i" & iRow).Value = "A - Almost Certain"
iconsequence = 1
ElseIf consequence = "B - Likely" Or LCase(consequence) = "b" Or
LCase(consequence) = "l" Or LCase(consequence) = "likely" Then
Range("i" & iRow).Value = "B - Likely"
iconsequence = 2
ElseIf consequence = "C - Possible" Or LCase(consequence) = "c" Or
LCase(consequence) = "p" Or LCase(consequence) = "possible" Then
Range("i" & iRow).Value = "C - Possible"
iconsequence = 3
ElseIf consequence = "D - Unlikely" Or LCase(consequence) = "d" Or
LCase(consequence) = "u" Or LCase(consequence) = "unlikely" Then
Range("i" & iRow).Value = "D - Unlikely"
iconsequence = 4
ElseIf consequence = "E - Rare" Or LCase(consequence) = "e" Or
LCase(consequence) = "r" Or LCase(consequence) = "rare" Then
Range("i" & iRow).Value = "E - Rare"
iconsequence = 5
'Convert to FMECA format, and set matrix column
End If
If likelihood = "5 - Catastrophic" Or likelihood = "5" Or
LCase(likelihood) = "ca" Or LCase(likelihood) = "5" Or LCase(likelihood) =
"catastrophic" Then
Range("j" & iRow).Value = "5 - Catastrophic"
ilikelihood = 5
ElseIf likelihood = "4 - Major" Or likelihood = "4" Or LCase(likelihood)
= "ma" Or LCase(likelihood) = "4" Or LCase(likelihood) = "major" Then
Range("j" & iRow).Value = "4 - Major"
ilikelihood = 4
ElseIf likelihood = "3 - Moderate" Or likelihood = "3" Or
LCase(likelihood) = "mo" Or LCase(likelihood) = "3" Or LCase(likelihood) =
"moderate" Then
Range("j" & iRow).Value = "3 - Moderate"
ilikelihood = 3
ElseIf likelihood = "2 - Minor" Or likelihood = "2" Or LCase(likelihood)
= "mi" Or LCase(likelihood) = "2" Or LCase(likelihood) = "minor" Then
Range("j" & iRow).Value = "2 - Moderate"
ilikelihood = 2
ElseIf likelihood = "1 - Insignificant" Or likelihood = "1" Or
LCase(likelihood) = "in" Or LCase(likelihood) = "1" Or LCase(likelihood) =
"insignificant" Then
Range("j" & iRow).Value = "1 - Insignificant"
ilikelihood = 1
Else
ilikelihood = "0"