Conditional Formatting w/blank CELL

[MR3]

how can i check a range of columns for a blank cell and then conditionally format the row to change color?

 

[stnkynts]

Run this: Where in this example the Range is Cells A1:A10

Option Explicit
Sub HighlightBlanks()
Dim myRange As Range:   Set myRange = Range("A1:A10")
Dim icell As Range
 
For Each icell In myRange
    If IsEmpty(icell) Then
        icell.EntireRow.Interior.ColorIndex = 3
    End If
Next icell
 
End Sub

 

[p45cal]

how can i check a range of columns for a blank cell and then conditionally format the row to change color?

1. Will you want to format the entire row across the whole sheet or just the table?
2.Do you want to check ALL columns in a table or just some of them?

Below, the conditional format was applied to all the cells (green and orange) at once by selecting the range tpo left to bottom right so hat a cell on the top row is the active cell, then placed the formula into conditional formatting.
The formatting only checks for blanks in columns R,S,T AND W, but highlights the whole row in the table.

Excel Workbook
	P	Q	R	S	T	U	V	W	X
4
5
6		asd	asd	asd	asd	asd	asd	asd
7		asd	asd	asd	asd	asd		asd
8		asd	asd	asd	asd	asd	asd	asd
9		asd	asd		asd	asd	asd	asd
10		asd	asd	asd	asd	asd	asd	asd
11		asd	asd	asd	asd	asd	asd	asd
12		asd	asd	asd	asd	asd	asd	asd
13		asd	asd			asd	asd	asd
14		asd	asd	asd	asd	asd	asd	asd
15		asd	asd	asd	asd	asd	asd	asd
16		asd	asd	asd	asd	asd	asd
17
18		asd	asd	asd	asd	asd	asd	asd
19		asd	asd	asd	asd	asd	asd	asd
20
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q6	1. / Formula is =(COUNTBLANK($R6:$T6)+COUNTBLANK($W6))>0	Abc

 

[T. Valko]

how can i check a range of columns for a blank cell and then conditionally format the row to change color?

What version of Excel are you using?

Use the "formula option" in conditional formatting and use a formula something like this:

=COUNTBLANK($A1:$C1)

 

[MR3]

I am using 2007. And I only want to check columns D to Z but down to rows occupied with actual data. column A will define what actual length of the data will be max. when Column A is blank there will be no more data below that therefore I dont want to highlight rows below that.

Another solution could be just to highlight blank cells in a given column range.

 

[p45cal]

I added to my last post (#3)

 

[T. Valko]

I am using 2007. And I only want to check columns D to Z but down to rows occupied with actual data. column A will define what actual length of the data will be max. when Column A is blank there will be no more data below that therefore I dont want to highlight rows below that.

Another solution could be just to highlight blank cells in a given column range.

So, you want to highlight the rows between columns D:Z if there are any empty cells on a row but only for those rows where the corresponding cell in column A has some entry?

 

[MR3]

When i do SHIFT+CTRL+DOWN ARROW, it doesnt select all the cells that i want.
How can i automate the script so it will select all cells with values?

 

[MR3]

to the top

 

[Joe4]

SHIFT+CTRL+DOWN ARROW select the Current Region, which is the Contiguous range that your cursor is in. This mean it will select all cells around that cell until it finds the first completely blank row and column. Since there are no entries in row 7, that is where it is stopping.

So you would need to go about it a bit differently if you have "breaks" in your data like that. Is there are column that will always have data in it, if there is data in that particular row? Putting it another way, for every row that has data in it, are there any columns that are guaranteed to have data in that instance?