CF : Formula To Highlighted Color If All Cells Fill Data

[muhammad susanto]

hi all..

how to make formula in CF with condition like this:
if the cell contains/fill all data (not empty) -- so highlighted with yellow otherwise condition all cell not highlighted color
here this sample

sample1	sample2
London	London
3	3
Hongkong		empty cell
-	-
-	-
paris	paris

anyone help, thanks in advance

.sst

 

[Eric W]

How about:

Book2
	A	B
1	Sample1	Sample2
2	London	London
3	3	3
4	Honkong
5	-	-
6	-	-
7	paris	paris
Sheet11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B7	Expression	=COUNTA(A$2:A$7)=6	text	NO

 

[muhammad susanto]

hi Eric

my question what is the meaning '6' in your formula

=COUNTA(A$2:A$7)=6

would you explained ?

 

[Eric W]

COUNTA counts how many cells are filled in a range. Your range is A2:A7, which has 6 cells in it.

 

[muhammad susanto]

hi Eric, i have new problem..
your formula in range A2:A7, i have over 50 samples like in range B2:B7, C2:C7, etc....

how to easy or simple organize your formula for 50 sample? for each sample are different data

 

[Eric W]

As long as all your ranges have the same rows, 2-7, the formula will work fine. If you look at my initial post, the formula was applied to A2:B7. So just select your entire range (A2:AZ7 or whatever it is), and add the CF formula as shown. If you have other ranges with other rows, like A10:G15, then change the row numbers in the formula to match those rows.

 

[muhammad susanto]

hi Eric, unfortunately for each sample is standalone table/column. They are not related..

 

[Eric W]

In that case, you'd need a separate rule for each sample. Maybe if there's some way to identify how many items are in each column, and if the columns are adjacent. If there are actual Excel tables, that adds another twist. If you could show a more representative sample of your data, I could take another look.