Conditional Formatting over different sheets in a workbook

Abhishek_gliot 

Ok, since you are starting with data in the table, please go to the Conditional Formatting Rules Manager using Home>Conditional Formatting>Manage Rules.

Then, click the arrow to edit the Applies To range:

Now put your cursor above the "order" column until you see a black arrow. When you see the black arrow, left-click so that the whole column is selected:

Then click the down-arrow on the Conditional Formatting Rules Manager shown above.

Now select the rule and click Edit Rule.

Select the part of the formula that says $A3 and then click on the first row in the technicalissues table. For you, this will be changing it to $A2:

When you first click the cell, it will show $A$2. You need to remove the second $. You can do this by deleting it, or by pressing F4 twice to cycle through relative and absolute references.

Next, highlight the part of the formula that gives the range to the Ready for Billing sheet:

While that is highlighted, go to the Ready for Billing sheet and again, select the entirety of column A by clicking on the A column header:

In the end, your rule formula will look something like this:

=NOT(ISERROR(VLOOKUP($A2,'Ready for billing'!$A:$A,1,FALSE)))

 
Now that you have deliberately selected the entire column for the lookup range, and repositioned your starting cell, you should have no problems with newly added rows.