Conditional Formatting in matrix structure

hennanra

New Member
Joined
Jan 10, 2006
Messages
16
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,397
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1Dept
2Job Position:EngineeringLegalFinance
3Supervisor13598367892
4Manager058413697
5Signal Op78955712135
6Operations Admin8974347890
CF vba
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Raheel

If you want specific help then give us specific information.:)
 

hennanra

New Member
Joined
Jan 10, 2006
Messages
16
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
 

Forum statistics

Threads
1,181,648
Messages
5,931,213
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top