Coloring a row given certain information

[trailblder]

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!!

 

[erik.van.geit]

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]

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]

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]

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]

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
	A	B	C	D	E
1	Drive	Date In	Date Out	Point of Contact
2	123456	02/02/2005		xyz
3	123478	15/08/2005	22/01/2006	abc
4	123499	05/01/2006	18/06/2006
5
6
7
Conditional Format

 

[trailblder]

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]

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]

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