Rather than a macro, why not use Conditional Formatting?

Assuming Name, Address, Contact and Status are in Columns A
to D, and the header is in row 1, then highlight the area
where you will be entering data. With the cursor on cell
A2 choose "Format" "Conditional Formatting" and change from
"Cell Value Is" to "Formula Is". Enter the formula
$D2="No" and then click on "Format...", "Patterns" and
choose the red colour.

Whenever "No" is input in column D, Columns A to D of that
row will be highlighted in red.

*** Sent via Developersdex http://www.developersdex.com ***