Conditional Formating a row if a cell is blank
How to Color an entire row if a particular cell in the row is blank ??
MacBook Pro, OS X Mavericks (10.9.4)
Select the cells and go to rules
4 replies
Hi Niranjan,
There are a couple of ways to do this. One approach is here:
conditional highlighting using a formula
It would not be hard to modify to fit your situation.
Another approach would be to add a row above your row and compare cells to it. How you would do that would depend on what sort of data is in the other cells.
quinn
Hi Niranjan,
I guess I don't need to know what your data is like for the extra row approach. This solution will not work if you want a zero entry to clear the highlighting. Column D has my blank cell.
B2 =IF($D2=0,B2,".")
I have 2 rules for the conditional highlighting.
text is (row 1 cell)
number is equal to (row 1 cell) this is only needed for the blank cell but is ok in the rest.
quinn
If you don't want extra rows in your table and just want a clean, two-click solution, then AppleScript is good at conditional coloring of rows.
Here, a script colors rows whose column C (column 3) is blank, like this:
a | b | c | d |
1 | 2 | 4 | |
1 | 3 | 4 | |
a | b | d | |
1 | 3 | 4 |
This is the script:
property startRow : 2 -- to skip a header row
property columnToTest : 3 --column C, change as needed, 2 for B, 4 for D, etc.
property highlightColor : "red"
tell application "Numbers"
try
set t to front document's active sheet's first table whose selection range's class is range
tell t to repeat with r from startRow to row count
set v to row r's cells's value
if v's item columnToTest is missing value then
set rowr'sbackground color to highlightColor
end if
end repeat
end try
end tell
To use it all that is needed is:
- Copy and paste into AppleScript Editor
- Click once anywhere in the table (to tell the script what table you mean)
- Click the green triangle 'Run' button.
(Change color and column as desired in properties.)
That's all it takes!
SG
Conditional Formating a row if a cell is blank