Make Cell change colour automatically

[Lolarsso]

Hello
I have a time consuming problem. I make a lot of risk analyzes and depending on the probability and the consequence the risk is marked with Green, Yellow or Red.
Now I change the colour manually but it would save a lot of time if the cell changed colour depending on the values in the previous collumns.

I thought that i could add a rule for the collumn but one value can have different colours.
If the Consequence (K) is 5 the colour will always be Red.
If the value is 10 or higher the colour is Red.
(See the picture)

Is there a way to make the cells automatic?

 

[Kerryx]

Book1
	A	B	C	D
1	Prob	Conq	Risk	aut Color
2	1	1	1
3	1	2	2
4	1	3	3
5	1	4	4
6	1	5	5
7	1	1	2
8	1	2	4
9	2	3	6
10	2	4	8
11	2	5	10
12	3	1	3
13	3	2	6
14	3	3	9
15	3	4	12
16	4	5	15
17	4	1	4
18	4	3	8
19	5	3	11
20	5	4	16
21	5	5	20
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D21	Expression	=OR($B2=5,$C2>=10)	text	NO
D2:D21	Expression	=AND($A2<=2,$B2<=2)	text	NO
D2:D21	Expression	=OR($A2>=3,$B2<=4)	text	NO

 

[Kerryx]

 

[Georgiboy]

I took a code index/match/match option:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Probability	Consequences	Risk	Manual	Auto
2	1	1							Consequence
3	1	1					Probability		1	2	3	4	5
4	1	2					VH	5	6	3	3	3	3
5	1	3					H	4	6	6	3	3	3		< colour index numbers
6	1	5					M	3	6	6	6	3	3
7	1	4					S	2	4	4	6	6	3
8	2	1					VS	1	4	4	6	6	3
9	2	1
10	2	1
11	2	1
12	2	2
13	2	3
14	2	5
15	3	4
16	3	1
17	3	1
18	3	1
19	3	1
20	3	2
21	3	3
22	4	5
23	4	4
24	4	1
25	4	1
26	5	1
27	5	1
28	5	2
Sheet1

Set up the sheet as above then run the below code:

Sub test()
    Dim rng As Range, rCell As Range
    Dim Prob As Integer
    Dim Con As Integer
    Dim ci As Integer
    
    Set rng = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)

    For Each rCell In rng.Cells
        Prob = rCell.Value
        Con = rCell.Offset(, 1).Value
        With Application
            ci = .Index(Range("I4:M9"), .Match(Prob, Range("H4:H9"), 0), .Match(Con, Range("I3:M3"), 0))
        End With
        rCell.Offset(, 4).Interior.ColorIndex = ci
    Next rCell
End Sub

 

[Kerryx]

oops had to tweak it a small bit and simplify a bit

Book1
	D
2
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D26	Expression	=AND($A2<=2,$B2<=2)	text	NO
D2:D26	Expression	=OR($B2=5,$C2>=10)	text	NO
D2:D26	Expression	=$A2+$B2>=7	text	NO
D2:D26	Expression	=OR($A2+$B2<7)	text	NO