CODEWITHSUNDEEP
vbaexcelexcel-2007conditional-formatting
m hanif f
m hanif f

Reputation: 418

Highlight cells based on some criteria related column and rows to another table

I have two worksheets: the first one is main (Table 1) and the second is the report (Table 2) generated from values in two columns in the main table. When the conditional formatting is triggered the cell in Table 2 is highlighted:

tabel 1

tabel 2

In Table 1, the primary key is a compound key combining id-year columns.
In Table 2, the report checks whether the related column-row exists in Table 1 and if so the cell is to be highlighted.

How I can achieve it using conditional formatting?

I've some steps that will converted to conditional formatting in cell below:

  1. In selected cell, the paired value year-id from table 2 will be looked up in Table 1 in the relevant column pairing.
  2. If the related paired-value exists, the cell in Table 2 is highlighted (the color differs between ids) and if not it won't be highlighted.

For Step 1. I can't find the right formula. If there is another solution I'll considered it.

Can Step 2. be achieved with VBA and if so, how?

[updated] Based on pnuts's suggestion, the problems above I can achieve with some modification to get vary color but recently I get different format value that appear in table 1 that look like "2003-2004". In second table, the related column (2003 & 2004) must be highlighted. How I can check "-" sign then highlight two related columns?

Upvotes: 0

Views: 1234

Answers (1)

pnuts
pnuts

Reputation: 59475

Assuming Table1 and Table2 are both in Cell B2, one way is to put =Sheet1!C5&Sheet1!D5 in your Table2 sheet in A5 and copy down until a cell appears blank, then apply CF to =$C$6:$M$11 with this rule: 

=MATCH($B6&C$5,$A:$A,0)>0

This would only apply one colour throughout (which may be less confusing than 5 or more) but I take it you know how to break this down into separate rules for different colours by restricting the range for each to one row at a time.

Upvotes: 1

Related Questions