If employee number 5263 found in sheet 2, show the raw in main data on sheet
1 in red, for instance.
If employee number 5263 found in sheet 2, show the raw in main data on sheet
1 in red, for instance.
Conditional formatting wont allow you to reference another worksheet, however, as a work around you could add a column and paste in the formula
=NOT(ISERROR(VLOOKUP(A1,Sheet2!A1:A7,1,FALSE)))
Where cell A1 contains the employee number and Sheet2!A1:A7 contains the employee numbers on the other sheet. This formula will return TRUE if the employee is on the other sheet and FALSE if it is not. You can then hide this column if you want and use the result in your conditional formatting by highlighting the range you want to conditionally format and for the first record say you put the formula above in column D you would select Formula and type in =$D1 and select the red font format.
---------------------------------------------------
ONLY APPLIES TO VBA RESPONSES WHERE APPROPRIATE
To insert code into the VBE (Visual Basic Editor)
- Copy the code.
- Open workbook to paste code into.
- Right click any worksheet tab, select View Code
- VBE (Visual Basic Editor) opens to that sheets object
- You may change to another sheets object or the This Workbook object by double clicking it in the Project window
- In the blank space below the word "General" paste the copied code.
You can reference another worksheet if you use a named range.
For example, if your employee numbers are in sheet 2 column A then select that column and use Insert > Name > Define to name that range, something like "employees" (without the quotes)
then in sheet 1 if your employee numbers are in column C, select that column and use CF with "formula is" option
=MATCH(C1,employees,0)
choose red format
that's it
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks