Excel Formula with Reference NAME and conditional formatting

[remeng]

Hi Folks,

I have 2 different sheets in the same workbook. On the first sheet "Error Rules" I want a user to enter a general formula into a cell that will reference the cell(s) NAME on the second sheet "data". The "data" sheet includes a table.

the information is loaded from a .CSV file into the "data" sheet.

"data" Sheet layout

Error Message_________Dept_________Prod_Loc

"Blank"__________________1367__________WCG
"Invalid Prod Loc"_______2000__________WCG

formula overview

=if(Dept = 1367 and Prod_Loc = WCG, , Highlight Dept and Product_Loc in yellow and add error msg "Invalid Prod Loc")

I'd like to take the formula from the cell on the "Error Rules" sheet and using a column cell NAME on the "data" sheet, run it for all the values in that column.

How can I do this with VBA? I've already tried the cells().formula method with the NAME + i where the formula would be copied to. The problem I am having is that the user needs to enter cell reference locations into the formula on the "Error Rules" sheet.

I've also tried using a custom formula, but I am not to familiar with that so I am sure I did something wrong.

Mike

 

[remeng]

I got this working. It was a coding error