Highlighting blank cells based on another value, but columns aren't always the same

[nfunkhouser]

I have a spreadsheet where I'm looking to highlight the Network ID if they put YES in the Network Requested field - the issue I'm having is that the people who submit these to us like to move things around, so I don't always know if the Network ID will be in the same column - sometimes it's in I, sometimes H, sometimes they move it to the end and it's in column BA. It's a pain, but sending them back isn't an option. Is there a way to do this using either VBA or conditional formatting, when I don't know what the column letters will be?

What it looks like:

What I'd like it to do - highlight the blank Network ID since they put YES in Network Requested but didn't provide a Network ID

 

[6StringJazzer]

The real solution to this is to protect the sheet so they can't move anything around.

You didn't give us any context for what your sheet looks like. I don't know what rows and columns you are using. I'll assume the first column is A, and headers are in row 1 and data starts in row 2.

The Applies To range for this rule will be $A$2:$Z$99 where $Z$99 to the last possible cell where you could have data. Change to suit:

=AND(INDEX(A:A,1)="Network ID",INDEX($A$2:$Z$99,ROW(A2)-1,MATCH("Network Requested",$A$1:$Z$1,0))="Yes")

I have not tested this because I did not take the time to type in the data from scratch from a picture, but you can try it and let me know how it works. Consider pasting in data directly from your post. The best solution is to use the XL2BB add-in which allows you paste in something pretty close to a spreadsheet.

 

[Peter_SSs]

Welcome to the MrExcel board!

Looks like formal Excel table so try Conditional Formatting like this in the Network ID column (don't forget to check the table name and column label)

nfunkhouser.xlsm
	B	C	D	E	F	G	H
4	Something	Network Requested	Other	Network ID	Phone	More 1	More 2
5		Yes		3319
6		Yes		8901
7		No
8		Yes
9		No
10		Yes		899	353
11		No
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:E11	Expression	=AND(E5="",INDIRECT("Table1[@Network Requested]")="Yes")	text	NO

Now if I drag some columns to different places ..

nfunkhouser.xlsm
	B	C	D	E	F	G	H
4	Something	Network ID	Other	More 2	Phone	Network Requested	More 1
5		3319				Yes
6		8901				Yes
7						No
8						Yes
9						No
10		899			353	Yes
11						No
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:C11	Expression	=AND(C5="",INDIRECT("Table1[@Network Requested]")="Yes")	text	NO

Is that the sort of thing you are after?

 

[kvsrinivasamurthy]

Since the size is data is large and the column of ID is not fixed, the entire data range is to be conditionally formatted. This may slow down excel. It is better to go for Macro. Is it ok .

 

[nfunkhouser]

Since the size is data is large and the column of ID is not fixed, the entire data range is to be conditionally formatted. This may slow down excel. It is better to go for Macro. Is it ok .

Yes, a macro would be fine - I just wasn't sure which was best for this situation.

 

[nfunkhouser]

Welcome to the MrExcel board!

Looks like formal Excel table so try Conditional Formatting like this in the Network ID column (don't forget to check the table name and column label)

nfunkhouser.xlsm
	B	C	D	E	F	G	H
4	Something	Network Requested	Other	Network ID	Phone	More 1	More 2
5		Yes		3319
6		Yes		8901
7		No
8		Yes
9		No
10		Yes		899	353
11		No
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:E11	Expression	=AND(E5="",INDIRECT("Table1[@Network Requested]")="Yes")	text	NO

Now if I drag some columns to different places ..

nfunkhouser.xlsm
	B	C	D	E	F	G	H
4	Something	Network ID	Other	More 2	Phone	Network Requested	More 1
5		3319				Yes
6		8901				Yes
7						No
8						Yes
9						No
10		899			353	Yes
11						No
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:C11	Expression	=AND(C5="",INDIRECT("Table1[@Network Requested]")="Yes")	text	NO

Is that the sort of thing you are after?

If I'm understanding correctly, this would require us to alter the formula each time we get a spreadsheet back, is that right? Unfortunately, it needs to be friendly enough to use for people who aren't familiar with higher end excel formatting - which is why I'm trying to create something to help automate it.

 

[kvsrinivasamurthy]

what is approx data range.

 

[nfunkhouser]

what is approx data range.

I'd say a safe range would be A1 through AZ500

 

[kvsrinivasamurthy]

Calender formation for Excel 365 version.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	AAA	BBB	CCC	Network Requested	EEE	ww	rr	FFF	GG	Network ID	ZZ	JJJ	KK	HN!
2	saas	BBB		Yes						22
3	saas			No						44
4	saas			Yes						55
5	saas			No
6	saas			Yes
7	saas			Yes						66
8	saas			Yes
9	saas			Yes						45
10	saas			Yes
11	saas			Yes
Sheet5

Data range assumed is up to column BZ, change as required in code.
VBA code.

Sub HighlightID()
Dim Ip As Range, hdr As Range
Dim Lr&, T&, ClmNR&, ClmID, P$
Dim M

Lr = Range("A" & Rows.Count).End(xlUp).Row
Set Ip = Range("A2:BZ" & Lr)
Set hdr = Range("A1:BZ1")

ClmNR = WorksheetFunction.Match("Network Requested", hdr, 0)
ClmID = WorksheetFunction.Match("Network ID", hdr, 0)
P = "transpose(If((" & Ip.Columns(ClmNR).Address & "=""Yes"")*(" & _
    Ip.Columns(ClmID).Address & "=""""),Row(" & Ip.Columns(ClmNR).Address & "),False))"
M = Filter(Evaluate(P), False, False)
For T = 0 To UBound(M)
Cells(M(T), ClmID).Interior.Color = 65535
Next T

End Sub

 

[nfunkhouser]

Calender formation for Excel 365 version.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	AAA	BBB	CCC	Network Requested	EEE	ww	rr	FFF	GG	Network ID	ZZ	JJJ	KK	HN!
2	saas	BBB		Yes						22
3	saas			No						44
4	saas			Yes						55
5	saas			No
6	saas			Yes
7	saas			Yes						66
8	saas			Yes
9	saas			Yes						45
10	saas			Yes
11	saas			Yes
Sheet5

Data range assumed is up to column BZ, change as required in code.
VBA code.

Sub HighlightID()
Dim Ip As Range, hdr As Range
Dim Lr&, T&, ClmNR&, ClmID, P$
Dim M

Lr = Range("A" & Rows.Count).End(xlUp).Row
Set Ip = Range("A2:BZ" & Lr)
Set hdr = Range("A1:BZ1")

ClmNR = WorksheetFunction.Match("Network Requested", hdr, 0)
ClmID = WorksheetFunction.Match("Network ID", hdr, 0)
P = "transpose(If((" & Ip.Columns(ClmNR).Address & "=""Yes"")*(" & _
    Ip.Columns(ClmID).Address & "=""""),Row(" & Ip.Columns(ClmNR).Address & "),False))"
M = Filter(Evaluate(P), False, False)
For T = 0 To UBound(M)
Cells(M(T), ClmID).Interior.Color = 65535
Next T

End Sub

Thank you, that worked just as I needed it to!