Cell background colour change based on value found on other sheet columns

[kennylal]

Hello Everyone,

I have a situation in excel while work allocation and I am in a need of help from you all excels masters. Please help based on below conditions.

- I have two sheets (sheet1) & (sheet2)
- In (sheet1) I have data in column (B,C,D,E)
- On (sheet2) I have data in four columns (A,B,C,D)

Output require is based on data available on "sheet2" I want to highlight cell colors on "sheet1"
(Condition-I: if "sheet2" value on column (A) is available on "sheet1" column (B) then highlight in (Red color)
(Condition-II: if "sheet2" value on column (B) is available on "sheet1" column (B) then highlight in (Blue color)
(Condition-III: if "sheet2" value on column (A) is available on "sheet1" column (B) then highlight in (Green color)
(Condition-IV: if "sheet2" value on column (A) is available on "sheet1" column (B) then highlight in (Orange color)

Please help!!

Thanks in advance!

 

[Fluff]

You're conditions don't look to be correct as they are contradictory.
But you can use Conditional formatting.
Create a new rule > Use formula > =MATCH(B1,Sheet2!$A:$A,0) > select format > OK
This will do the first criteria

 

[kennylal]

Thank you for attending my query!
Actually currently I am using the below code which works for one color "Red" ("it is comparing data from "Priority Apt Code" considered as sheet2 (refer above post) from column "c" with "Input data" sheet1 on column "B" if value matches then it is highlighting the value in red on "Input data" sheet column "B")
This code is on command button click event.


Sub CompareAndHighlight()

Dim rng1 As Range, rng2 As Range, i As Long, j As Long
For i = 1 To Sheets("Input Data").Range("B" & Rows.Count).End(xlUp).Row
Set rng1 = Sheets("Input Data").Range("B" & i)
For j = 1 To Sheets("Priority Apt Code").Range("C" & Rows.Count).End(xlUp).Row
Set rng2 = Sheets("Priority Apt Code").Range("C" & j)
If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
rng1.Font.Color = vbRed
End If
Set rng2 = Nothing
Next j
Set rng1 = Nothing
Next i

End Sub


now I want to add similarly three more comparing conditions in the above code which compare data from "Priority Apt Code" sheet column "D","E"& "F" as well and if values found then highlight in any other color on "Input data" sheet column "B".

I hope I am able to explain now.

Please revert..

Thanks,
Kenny

 

[Fluff]

Why do you need to use a macro, rather than conditional formatting?