Hi guys,

I"m making a simple data entry form that requires 14 boxes to be filled out. I'm trying to make it so that when the 14 boxes are all filled in, all 14 cells will turn green to symbolize that all boxes have been filled in.

I have tried to google solutions but all I can find is for the entire row to be highlighted if one is blank but that doesnt work for me since I have hidden columns inbetween which i think messes things up..

I hope that made senese!

Wcngu1 wrote:

I"m making a simple data entry form that requires 14 boxes to be filled
out. I'm trying to make it so that when the 14 boxes are all filled in,
all 14 cells will turn green to symbolize that all boxes have been
filled in.

I have tried to google solutions but all I can find is for the entire
row to be highlighted if one is blank but that doesnt work for me since
I have hidden columns inbetween which i think messes things up..

I hope that made senese!

How about conditional highlighting? Set 2 rules for those 14 cells:
- Cell value not equal to "", fill the cell with green
- Cell value equal to "", fill the cell with red

--
I'm gonna drop you like third period French.

How about conditional highlighting? Set 2 rules for those 14 cells:
- Cell value not equal to "", fill the cell with green
- Cell value equal to "", fill the cell with red

--
I'm gonna drop you like third period French.[/quote]


Thanks for the reply! Its just that I dont want any of the cells to turn green until 8 specific cells in the row have been filled in and are no longer blank, giving me a bit of a headache haha

Try...

Select the 14 boxes (cells) and give them a defined name with local
(sheet-level) scope, then while they are still selected enter the
following Conditional Formatting formula...

=COUNTA(Inputs)=14

...where the range is named as follows...

Name: 'Sheet1'!Inputs

...and set your fill color.

Replace 'Sheet1' with the actual sheetname, and be sure to wrap it in
apostrophes (required if it has spaces) followed by the exclamation
character.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

[quote='GS[_2_];1615396']Try...

Select the 14 boxes (cells) and give them a defined name with local
(sheet-level) scope, then while they are still selected enter the
following Conditional Formatting formula...

=COUNTA(Inputs)=14

...where the range is named as follows...

Name: 'Sheet1'!Inputs

...and set your fill color.

Replace 'Sheet1' with the actual sheetname, and be sure to wrap it in
apostrophes (required if it has spaces) followed by the exclamation
character.

--
Garry



Thanks Gary, I should really explain my situation more, I"m making a really long check list Where I have:

-Employers name
-Task
-Start date
-Finish date
-Start time
-Finish time

and a few more criteria all on one row (with a few hidden rows in-between which dont matter as much)

So when employees fill out each row with the above criteria and fill in all the required cells, I"m hoping the row will go green to signify that yes all the boxes have been filled and you haven't missed anything.

I can see how your solution would work by giving them a defined name but would that mean I would have to slowly name each row? I"m hoping to conditional format to make it 20,000+ rows (or endless but thats probably unrealistic haha)

I'm not that advanced in excel and am unsure. I"m gonna have a crack at your solution to see if it works for multiple rows efficiently!

Cheers

You can make the defined name row-relative by removing the $ symbol as
follows...

$A1:$G1

...so the name becomes column-absolute, row-relative.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

[quote='GS[_2_];1615398']You can make the defined name row-relative by removing the $ symbol as
follows...

$A1:$G1

...so the name becomes column-absolute, row-relative.

Brilliant Gary thanks for that worked like a charm!

Cheers =)

You're welcome! I appreciate the feedback...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com