Unable to get the row number to change in the formula for conditional formatting, when copying the formatting down the column

[TParko]

Hi,

I am using the following formula =ISBLANK(Sheet1!$C2) to change the color of a cell when the cell reference 'is blank' on another sheet within my workbook.

When I copy the formatting down the column, the row number is not changing. I have tried the following versions of my cell reference $C$2, $C2 and C2, all with no success.

I'm hoping to avoid having to amend the formula for the conditional formatting for each row separately as my spreadsheet has 100 rows!

Please help

 

[mamady]

Hi Dear,

Instead of formatting based on a formula, please use the select "Format only Cells that contant" --> from the drop list choose "Blanks" and change the format accpordingly.

Regards

 

[Peter_SSs]

Instead of formatting based on a formula, please use the select "Format only Cells that contant" --> from the drop list choose "Blanks" and change the format accpordingly.

I think you may have missed that the OP is trying to format cells on one sheet based on what is in another sheet, not what is in its own cells.

change the color of a cell when the cell reference 'is blank' on another sheet

@TParko
Welcome to the MrExcel board!

When you are looking at Conditional Formatting rules the dialog always shows you what the rule is for the first cell in the range, not the one you may have selected. Example here I have selected A6 but the CF dialog is showing me the row 2 formula since that is where I started my CF

So your formula probably is adjusting behind the scenes for the correct row. If it is not working as you expect, could it be that the cells in column C of Sheet1 contain formulas returning "" since ISBLANK will return FALSE for those cells.

Perhaps you could try this CF instead

TParko 1.xlsm
	A
1
2
3
4
5
6
7
8
9
10
11
12
13
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A13	Expression	=Sheet1!$C2=""	text	NO

Here are my two sheets side-by-side. Is that what you are trying to achieve?

Note though that if you are trying to match the rows on each sheet, this would fail if rows are added/deleted on one sheet but not the other. If that could be the case, another approach would be needed.

 

[mamady]

@Peter_SSs Yes indeed, I missed that part. Thanks for bringing that to my attention