Coloring a row given certain information

trailblder

New Member
Joined
Oct 24, 2006
Messages
5
I am sure this is a pretty basic task, but I am a novice with Excel. I am making a simple inventory spreadsheet for work, keeping track of hard drives. I have drive serial number on the left for rows, then things such as date in/date out, po#, etc. for the columns. What I want to do is have the row be highlighted in a certain color (most likely red) when all the cells have not been populated. When all cells have been filled with the necessary information, the color will vanish, leaving just the "no fill" color (white). I would like this to load automatically when the spreadsheet is opened, and refresh when a new drive is entered on it as well. Thanks for the help!!
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, trailblder
Welcome to the Board !!!!!

try conditional format, which you can find in the menu format
you will need the function COUNTA and compare this to the number of columns which need to be filled

try this
select A1:H... (any number of rows)
conditional format
formula
=COUNTA($A1:$H1)<8
set color as required
OK

kind regards,
Erik
 

trailblder

New Member
Joined
Oct 24, 2006
Messages
5
Still a little confused

I guess I am a little confused on the anatomy of the formatting.
I did this:
=COUNTA($B3:$E7)<8
B3 is where my first blank cell started, E7 was the last empty cell. I set the color, and started populating cells.
I get to the last row however, B7 to E7 and the color won't vanish? Not sure what I didn't do!!
It seems the color always vanishes on the row one row behind of where I am working (for example if I am on row 4, row 3 is still colored despite all cells populated and row 2 is no color, which is what I want, immediately after all cells are filled).

I am probably screwing up the formula somewhere...and to be perfectly honest I don't know what the <8 does for me?




[/img]
 

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
Sorry to butt in here:

trailblder,

Welcome to the board!

Try this:

Format|Conditional Format and choose Formula is: from the drop down and enter =COUNTA($B3:$E7)<4, and set the format you request (use light colors for eventual printing purposes, plus it is easier to see as you fill it out on the screen)
and to be perfectly honest I don't know what the <8 does for me
B = first counted row, C = second counted row.... B:E = 4 rows to be counted.

Hope this helps.

RAM

Thanks Erik. I learned too.
 

trailblder

New Member
Joined
Oct 24, 2006
Messages
5

ADVERTISEMENT

Almost!!

LOL I am so close...very excited.. Thanks for the help so far everyone...and thanks for the welcome to the forums...I meant to say that a post ago, my apologies for my manners.

So that solved the colored row staying after all cells are populated.

The last bit to this puzzle is I would like the row to stay colored until ALL cells have been filled for column values.

________date in_________date out_______point of contact

123456
123478
123499

For example if I have row 123456's "date in" and "date out" filled in, but not "point of contact," I want the row to stay colored until "point of contact" has been filled in. Once POC has been filled, color vanishes.

As it stands now, the rows can have have missing column values and still the color will vanish when I begin work on the next row.

What I am trying to do is alert the operator of the spreadsheet that there is missing information for the hard drives.

Thanks again!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,871
Office Version
  1. 365
Platform
  1. Windows
trailblder

Here is a slightly different attempt. For this data:

1. Select A2:D10 (note past end of data)
2. Format|Conditional Formatting...|Condition 1|Formula is: =($A2<>"")*(COUNTA($B2:$D2)<3)|Format...|Patterns|choose colour|OK|OK

This way the row is white until something is entered in Column A and then it stays coloured until all 4 columns have data. By first selecting down to row 10 (or further if you want), those rows are ready to become coloured as soon as column A is completed in that row.
Mr Excel.xls
ABCDE
1DriveDate InDate OutPoint of Contact
212345602/02/2005xyz
312347815/08/200522/01/2006abc
412349905/01/200618/06/2006
5
6
7
Conditional Format
 

trailblder

New Member
Joined
Oct 24, 2006
Messages
5

ADVERTISEMENT

That did it

Peter, thanks a bunch!! I had to mess around with the column/row values a little, but it works like a champ. Thanks alot!!

As an aside does anyone know a good book for beginner formulas in excel? As well as one that goes in depth into syntax/terminology?

I will continue to cruise the forums to see what else I can pick up!

Thanks RAM and Erik for your contributions as well!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,871
Office Version
  1. 365
Platform
  1. Windows
Re: That did it

Peter, thanks a bunch!! I had to mess around with the column/row values a little, but it works like a champ. Thanks alot!!
You're welcome :)
As an aside does anyone know a good book for beginner formulas in excel? As well as one that goes in depth into syntax/terminology?
No. Use this forum, use the built-in Excel Help (the formulas with examples are in there), lots of trial and error.
 

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
Thanks RAM and Erik for your contributions as well!
You are very welcome.
Use this forum, use the built-in Excel Help (the formulas with examples are in there), lots of trial and error.
This forum has been my main sorce of knowledge. You kan tailormake your need of knowledge and go right on the subject without having to study everything about it.

Good luck

RAM
 

Watch MrExcel Video

Forum statistics

Threads
1,114,061
Messages
5,545,765
Members
410,704
Latest member
Cobber2008
Top