Conditional Format or VBA code - would need help with both. I want to change the fill colour of a cell based on another cell's content (text)

netrixuser

New Member
Joined
Jan 21, 2019
Messages
29
I just can't get my head around how to approach this.
I have inherited a spreadsheet containing a floorplan that is using Vlookups to pull the name of the person sitting at the desk from an array containing the desks and usernames. I would like to change the fill colour of the cell that represents the desk based on some text contained in another column in the lookup array. The text could be either T4 or T4M and I would like the fill colour to be different for each one.

Below is an example of the Desk layout and the Array below that. [The Vlookup references an cell containing both first and second names]
Based on the text in column I in the array, I would like the cells in the plan (where the names are presented) to change colour accordingly.

I can use Conditional Formatting in each cell but cannot see how to copy it to every other cell so that the referenced cell changes each time [ie to look at L1-2D01D for the cell containing Micky and L1-2D02D for Donald] . The Desk Plan, as you can imagine, is fairly large, but is not contiguous rows or columns of desks - but rather blocks of desks.

I'm happy to run a Macro as and when changes are made if that is an easier approach.

Any pointers/help gratefully received !

Regards, NetrixUser

Realised I missed the headings off - "Micky Mouse" is J58, "Donald Duck" is K58

ExampleFP.png
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,355
Office Version
  1. 2013
Platform
  1. Windows
@netrixuser

Maybe along these lines....


MRXLMAY21.xlsm
ABCD
1
2L1-2B01D
3L1-2B02DDonaldTrumpT4
4L1-2B03D
5L1-2B04D
6L1-2D01DMickyMouseT4
7L1-2D02DDonaldDuckT4M
8L1-2D03D
9L1-2D04D
Sheet7 (2)



MRXLMAY21.xlsm
JKLMNOPQ
57L1-2D01DL1-2D02DL1-2D03DL1-2D04D
58Micky MouseDonald Duck
59L1-2B02D
60  Donald Trump
61
62
Sheet7 (2)
Cell Formulas
RangeFormula
J60:L60,J58:M58J58=IFERROR(VLOOKUP(J57,$A$2:$D$9,2) & CHAR(10) & VLOOKUP(J57,$A$2:$D$9,3),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J57:Q62Expression=VLOOKUP(J57,$A$2:$D$9,4)="T4M"textNO
J57:Q62Expression=VLOOKUP(J57,$A$2:$D$9,4)="T4"textNO


Hope that helps.
 

Forum statistics

Threads
1,140,929
Messages
5,703,225
Members
421,285
Latest member
Bebek

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