Conditional Formatting in matrix structure

[hennanra]

Hello,

I have a spreadsheet which is in the form of a matrix - Costs by Job Position and Dept. I have an extremely simplified example below - but should suffice for illustration:

Dept:
Job Position: Engineering Legal Finance
Supervisor 1359 836 7892
Manager 0 5841 3697
Signal Op 7895 5712 135
Operations Admin 89743 4789 0

I need to use conditional formatting to determine if a given combination appears to colour the value cell a certain colour. For example, if we take the value for a supervisor within legal dept, the value cell might be yellow, whereas if it is supervisor and finance it might be green. The values themselves are not relevant to the formatting, although it is the value cell that will be formatted based on the dept and job position.

My initial approach is to build a separate worksheet with the combinations in a column. That is something separate. Assuming I had a set of criteria to look up - how would I achieve this (NB. I can do VBA but would like some specific rather than theoretical examples). I was thinking that if I had a separate list of the combined criteria - I could do an if statement nested in VBA. But am really looking for some more tangible ideas and examples to know if my approach is wrong.

Regds,
Raheel

 

[Peter_SSs]

See if this is any use. The sheet below was produced with the following (incomplete) code:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CF()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> CI <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("B3:D6")
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Cells(c.Row, 1).Value & _
                Cells(2, c.Column).Value
            <SPAN style="color:#00007F">Case</SPAN> "SupervisorEngineering"
                CI = 3
            <SPAN style="color:#00007F">Case</SPAN> "SupervisorLegal"
                CI = 4
              
             <SPAN style="color:#007F00">' Fill in other cases here</SPAN>
                
            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
                CI = 0
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
        c.Interior.ColorIndex = CI
    <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Excel Workbook
	A	B	C	D
1	Dept
2	Job Position:	Engineering	Legal	Finance
3	Supervisor	1359	836	7892
4	Manager	0	5841	3697
5	Signal Op	7895	5712	135
6	Operations Admin	89743	4789	0
CF vba

 

[Norie]

Raheel

If you want specific help then give us specific information.

 

[hennanra]

Many Apologies for not replying. Thank you very much Peter for taking the time to suggest a solution. I am not quite sure what happened but I was not notified when you replied to this post. I am going to double check my settings.

Due to difficulty in getting help elsewhere (and not being aware of your reply) - I found a workaround to meet the deadline - but the code you have provided is very useful as this is an ongoing exercise - thank you once again.

Kind Regards,
Raheel