Need to delete color and no data after blank column

[sksanjeev786]

Hi Team,

I have huge data and i don't want color if it blank cell and also i don't want data if data is available after empty cell.

1. For example, E7 is my last data after that it has a blank cell so i don't want data after that...
2. Also i want to highlight if all scores are same in the same row let assume C6 to H6 has same data 325 in all row then highlighted with yellow

Base_.xlsx
	A	B	C	D	E	F	G	H
3	Overall Results 1	Overall Results	1265
4	Banner Book 1	Age	294	392	287	292		951
5	Banner Book 2	Current Vehicle	31	48	43	19	163	160
6	Banner Book 3	Decision Factors	325	274	162	727	512	280
7	Banner Book 4	Decision Maker	784	388	43		2687
Sheet1

Cell Formulas
Range	Formula
C3:H7	C3	=IF(INDEX(INDIRECT("'"&$A$2&$A3&"'!A1:IV500"),MATCH($A$1,INDIRECT("'"&$A$2&$A3&"'!A1:A500"),0),C$1)=0,"",INDEX(INDIRECT("'"&$A$2&$A3&"'!A1:IV500"),MATCH($A$1,INDIRECT("'"&$A$2&$A3&"'!A1:A500"),0),C$1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:AJ53	Cell Value	contains """"	text	NO
C7:AJ7	Cell Value	duplicates	text	NO
C6:AJ6	Cell Value	duplicates	text	NO
C5:AJ5	Cell Value	duplicates	text	NO
C4:AJ4	Cell Value	duplicates	text	NO
C3:AJ3	Cell Value	duplicates	text	NO

 

[DanteAmor]

You only need one rule in conditional formatting and adjust the formula:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
3	Overall Results 1	Overall Results	25	26	27	28	29	30
4	Banner Book 1	Age	25	25	25	25	25
5	Banner Book 2	Current Vehicle	25
6	Banner Book 3	Decision Factors	12	16	12
7	Banner Book 4	Decision Maker	25	30	26
sh

Cell Formulas
Range	Formula
C3:J7	C3	=IF(B3="","",IF(INDEX(INDIRECT("'"&$A$2&$A3&"'!A1:IV500"),MATCH($A$1,INDIRECT("'"&$A$2&$A3&"'!A1:A500"),0),C$1)=0,"",INDEX(INDIRECT("'"&$A$2&$A3&"'!A1:IV500"),MATCH($A$1,INDIRECT("'"&$A$2&$A3&"'!A1:A500"),0),C$1)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:AJ7	Expression	=AND(COUNTIF($C3:$AJ3,C3)>1,C3<>"")	text	NO

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[sksanjeev786]

You only need one rule in conditional formatting and adjust the formula:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
3	Overall Results 1	Overall Results	25	26	27	28	29	30
4	Banner Book 1	Age	25	25	25	25	25
5	Banner Book 2	Current Vehicle	25
6	Banner Book 3	Decision Factors	12	16	12
7	Banner Book 4	Decision Maker	25	30	26
sh

Cell Formulas
Range	Formula
C3:J7	C3	=IF(B3="","",IF(INDEX(INDIRECT("'"&$A$2&$A3&"'!A1:IV500"),MATCH($A$1,INDIRECT("'"&$A$2&$A3&"'!A1:A500"),0),C$1)=0,"",INDEX(INDIRECT("'"&$A$2&$A3&"'!A1:IV500"),MATCH($A$1,INDIRECT("'"&$A$2&$A3&"'!A1:A500"),0),C$1)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:AJ7	Expression	=AND(COUNTIF($C3:$AJ3,C3)>1,C3<>"")	text	NO

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Thank you so much, Sir

For your support on this