Solved VBA collect cell and change BG color

February 4, 2020 at 22:58:33
Specs: Windows 10
Hello, everyone, I need help
I need to compare the data about worksheet1 and worksheet 2 all columns.
And I need the worksheet 2 to show the result by changing the BG color(Row) if which the data is not matching with the worksheet 1.
example
Worksheet1

Name_Department ID Name Department Salary
Mahesh_IT 12201 Mahesh IT 25,000
Rekha_HR 12202 Rekha HR 28,000
Ankush_PR 12205 Ankush PR 18,000
Sashi_IT 12206 Sashi IT 32,000
Rishi_HR 12207 Rishi HR 21,000
Shikha_Sales 12209 Shikha Sales 18,000
Rini_Support 12210 Rini Support 32,000
Vrish_IT 12211 Vrish IT 21,000
Vishu_Sales 12214 Vishu Sales 16,000
Sachin_PR 12215 Sachin PR 23,000
Ashwin_IT 12216 Ashwin IT 21,000
Ayush_HR 12217 Ayush HR 22,000
Dhruv_Sales 12219 Dhruv Sales 32,000

Worksheet2

Name_Department ID Name Department Salary
Mahesh_IT 12201 Mahesh IT 30,000
Rekha_HR 12202 Rekha HR 28,000
Ankush_PR 12205 Ankush PR 50,000
Sashi_IT 12206 Sashi IT 32,000
Rishi_HR 12207 Rishi HR 21,000
Shikha_Sales 12209 Shikha Sales 18,000
Rini_Support 12210 Rini Support 32,000
Vrish_IT 12211 Vrish IT 70,000


See More: VBA collect cell and change BG color


#1
February 5, 2020 at 03:29:10
First, a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.

Thanks!

As for your question, you said "all columns". A quick glance at your example seems to show that only the salary column is different. Is that always the case?

I also see that your data matches row-by-row as far as the name, etc. Is that always the case?

Finally, it appears that you have more data on Sheet 1 than on Sheet 2. Is that accurate or did you just not include all of Sheet 2? I ask because based on your data, the empty cells after Vrish_IT on Sheet 2 technically don't match Sheet 1 and therefore should have the background color changed.

Please clarify this for us.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
February 12, 2020 at 13:17:41
✔ Best Answer
After putting your data into Sheet1 and Sheet2 I got the following ranges:
Sheet1 with A1:E14
Sheet2 with A1:E9

I added the following formula into Conditional Formatting of Sheet2 after selecting range A2:E9:

=INDEX(Sheet1!$E$2:$E$14;MATCH($A2;Sheet1!$A$2:$A$14;0))<>$E2

I set yellow shading and the above formula colored rows for Mahesh_IT, Ankush_PR and Vrish_IT, all of which have different salaries in Sheet2 vs Sheet1.

I hope this helps.

message edited by Mrrrr


Report •
Related Solutions


Ask Question