Conditional Formatting based on text value in cell above

NWPhotoExplorer

New Member
Joined
Jan 19, 2021
Messages
21
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I'm working on a sheet that I use to keep track of my time worked and make sure it matches my paycheck. I have a few fields that have different headers, based on the type of payment received. I am trying to figure out how to write a conditional formatting formula that will look at the header title and then go to a specific field and see how many hours were there and match it to what was entered of the paycheck. It will color code it RED if I was shorted and GREEN if it matches.

As I am new to excel, I am still learning which functions to use and when, so I am not really sure what I should use here. Basically, I want to say if header Z1 = ABC PAY, look in J2 and if the value in Z2 matches J2, color it green, if not red. But I don't know how to go about setting that up in a conditional format. I am hoping someone here can help me get this to work correctly.

Thanks!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,913
Office Version
  1. 2016
Platform
  1. Windows
Hi NWPhotoExplorer,

Is this what you wanted?

NWPhotoExplorer.xlsx
JKYZ
1ABC PAY
22525
32522
43333
53344
6
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Z2:Z12Expression=AND($Z$1="ABC PAY",J2<>Z2,J2<>"")textNO
Z2:Z12Expression=AND($Z$1="ABC PAY",Z2=J2,Z2<>"")textNO
 

NWPhotoExplorer

New Member
Joined
Jan 19, 2021
Messages
21
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
@
Hi NWPhotoExplorer,

Is this what you wanted?

NWPhotoExplorer.xlsx
JKYZ
1ABC PAY
22525
32522
43333
53344
6
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Z2:Z12Expression=AND($Z$1="ABC PAY",J2<>Z2,J2<>"")textNO
Z2:Z12Expression=AND($Z$1="ABC PAY",Z2=J2,Z2<>"")textNO
That is pretty close to what I'm after. How do I set it up to look for just ABC or Abc. That way incase I don't have the labels right, the conditional formatting works based on abc and is not case dependent.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,913
Office Version
  1. 2016
Platform
  1. Windows
The check isn't case dependent.

NWPhotoExplorer.xlsx
JKYZ
1aBc pAy
22525
32522
43333
53344
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Z2:Z12Expression=AND($Z$1="aBC PAY",J2<>Z2,J2<>"")textNO
Z2:Z12Expression=AND($Z$1="ABC pay",Z2=J2,Z2<>"")textNO
 

NWPhotoExplorer

New Member
Joined
Jan 19, 2021
Messages
21
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Is there a way to do it so it only looks for "abc" and not ABC PAY? That way in case there is an extra space or something that someone else does, it just has to have abc or xyz or whatever else I set up in the search parameter.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,913
Office Version
  1. 2016
Platform
  1. Windows
Is there a way to do it so it only looks for "abc" and not ABC PAY? That way in case there is an extra space or something that someone else does, it just has to have abc or xyz or whatever else I set up in the search parameter.
Hold on! Are you saying it may not be in column Z? If so then what column range should be searched?

If it's the case you enter a name and that must be somewhere in Z1 then try this

NWPhotoExplorer.xlsx
JKYZAAAB
1JIM PayName
22525Jim
32522
43333
53344
6
Sheet1 (2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Z2:Z12Expression=AND(ISNUMBER(SEARCH($AB$2,$Z$1)),J2<>Z2,J2<>"")textNO
Z2:Z12Expression=AND(ISNUMBER(SEARCH($AB$2,$Z$1)),J2=Z2,J2<>"")textNO
 

NWPhotoExplorer

New Member
Joined
Jan 19, 2021
Messages
21
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
The field will stay in column Z.

Here is a rough version of what I am working on.
Work Hours.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1WeekStart DateEnd DateHoliday HoursPTO HoursRegular HoursJury DutyFMLA HoursOvertimeTotal HoursPTO TotalPTO AccrualCheck DatePTO AccruedPTO BalanceOther PayTotal Hours
Sheet1


Basically I am wanting to be able to type in Regular Pay and it looks in H and grabs the value. Or if I type in Jury Duty Pay, it finds Jury Duty in column J.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,913
Office Version
  1. 2016
Platform
  1. Windows
I'm not seeing how that relates to setting of a Conditional Format and there would be no match of Regular Pay or Jury Duty Pay as neither text is in the headings, although Regular and Jury both are.

This lets you type something in A1 and searches for a partial match in the headings and returns the same row value under that heading.

NWPhotoExplorer.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1RegularWeekStart DateEnd DateHoliday HoursPTO HoursRegular HoursJury DutyFMLA HoursOvertimeTotal HoursPTO TotalPTO AccrualCheck DatePTO AccruedPTO BalanceOther PayTotal Hours
2712345678910111213141516171819202122232425262728
Sheet2
Cell Formulas
RangeFormula
A2A2=INDEX($B$1:$AC$9999,ROW(),MATCH("*"&A1&"*",$B$1:$AC$1,0))
 

Forum statistics

Threads
1,141,049
Messages
5,703,942
Members
421,321
Latest member
blusky4

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