# Extrapolating Conditional Formatting

#### Dan Wilson

##### Active Member
Good day. I am using Excel 2002 on Win XP Pro. I have a workbook that audits the sales of sponsor flags for a golf tournament. There are currently 164 rows of data in the workbook. Column L contains the sponsor name and Column M contains a blank if the sponsorship is not yet paid and an "X" if it is paid. The user wants to be able to see at a glance which sponsors are still unpaid. Obviously, the answer is to view Column M. The user wants me to make the sponsor name (Column L)come up in a Pattern of yellow if unpaid to make it easier to find.

I have created the Conditional Formatting using Formula Is and all is well.

Is there a way to extrapolate the conditional formatting from one row to the next and have the formula automatically adjusted to look at Column M in that row? Otherwise, I will have to insert the conditional formatting into all of the rows at once and then edit the conditional format in each row to point to that row.

Thanks for any help.
Dan...

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### barry houdini

##### MrExcel MVP
You can achieve this in one step.

Select column L

Use this formula in conditional formatting

=(L1<>"")*(M1="")

select yellow formatting

The formula will automatically adjust for each row

#### Dan Wilson

##### Active Member

Good day Barry,

Wow! I don't understand how it works, what it is doing or why, but it works! Thank you.

Dan...

#### Scott Huish

##### MrExcel MVP
Numerically speaking, 0 evaluates as FALSE and non-zero evaluates as TRUE.
Doing math with a logical (Boolean) expression on the spreadsheet coerces TRUE to 1 and FALSE to 0.

If both conditions are true, this would evaluate as 1*1=1 or TRUE
if either condition is false, this would evalute to 1*0=0, 0*1=0 or 0*0=0 or FALSE

Conditional formatting formats when the expression evaluates to TRUE which 1 does.

#### Dan Wilson

##### Active Member

Good day Hotpepper. Thanks for the response. After I thought about it for a while, I see what is happening. What a simple process! I will remember this in the future.
Thanks again for taking the time to respond.
Dan...

Replies
7
Views
155
Replies
2
Views
387
Replies
0
Views
80
Replies
7
Views
183
Replies
2
Views
248

1,190,677
Messages
5,982,216
Members
439,769
Latest member
trungminh2802

### 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.

### Which adblocker are you using?

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

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