Auto Remark Text and color columns based on condition

sanket_sk

Board Regular
Joined
Dec 27, 2016
Messages
52
Office Version
  1. 365
Platform
  1. Windows
Dear All,

I am looking for excel formula or VBA code which will check different cells and highlight in case of repeat or more than approved value.
Refer below sample

1> Here excel will check GCS code (B4), Name(C4), Claim Type (D4) - with rest of the below cells in case of repeat entry excel should highlight repeat line with red color and put remark in H column.

2> At the same time excel will also check GCS code (B7), Claim Type (D7) and Amount (E7) - if found more than approved amount in table 2 excel should highlight repeat line with orange color and put remark in H column.

Could you please help me in doing this...

Samp.jpg
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
Cross posted Auto Remark Text and color columns based on condition

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

sanket_sk

Board Regular
Joined
Dec 27, 2016
Messages
52
Office Version
  1. 365
Platform
  1. Windows
Cross posted Auto Remark Text and color columns based on condition

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
Hi there,

I understand this and will not be repeated again, sorry for inconvenience.

Sanket
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you have available.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
According to your profile on Ef you are using 365, so how about one of these option
MISC Tracker.xlsx
ABCDEFGHI
1
2
3BranchGCS CodeNameClaim TypeAmountClaim MonthStatusRemarks
4DelhiABC1001HitechUnclaimed Calls Claim100001-Jan  
5DelhiABC1001HitechUnclaimed Calls Claim100001-JanRepeat claimRepeat claim
6DelhiABC1002DigitronsCourier - Courier charges incurred in providing repairs1000  
7DelhiABC1003SoumyaCourier - Courier charges incurred in providing repairs1200Claim amount too largeClaim amount to large
8
MISC Claim Tracker
Cell Formulas
RangeFormula
H4:H7H4=IF(COUNTIFS(B$4:B4,B4,C$4:C4,C4,D$4:D4,D4,F$4:F4,F4)>1,"Repeat claim",IF(FILTER(References!$D$3:$D$7,(References!$C$3:$C$7=B4)*(References!$B$3:$B$7=D4),"x")<E4,"Claim amount too large",""))
I4:I7I4=IF(COUNTIFS(B$4:B4,B4,C$4:C4,C4,D$4:D4,D4,F$4:F4,F4)>1,"Repeat claim",IF(IFERROR(INDEX(References!$D$3:$D$7,MATCH(B4&"|"&D4,References!$C$3:$C$7&"|"&References!$B$3:$B$7,0)),"ZZ")<E4,"Claim amount to large",""))
 

Watch MrExcel Video

Forum statistics

Threads
1,114,191
Messages
5,546,476
Members
410,742
Latest member
WalterSil
Top