Conditional format - Nth column to End

[Formula11]

How do you apply conditional format from the Nth column to the end.
Or, to the entire worksheet, except the first "N-1" columns.
Or, from cell G1 to the end of worksheet.

Example below is from 7th column to end.

Also to account for columns being added later.

 

[Peter_SSs]

Why not just manually format the columns and save the volatility of the formatting being conditional?

If it is because you may be inserting or deleting columns then select all the columns or range you want this to apply to starting from column A (& remembering the volatility issue) and apply this CF

22 02 01.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4
CF Columns

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:Q16	Expression	=COLUMN()>5	text	NO

 

[Formula11]

Thanks for posting.
I don't know what you mean though as I'm not very familar with conditional formatting.

I need to put something in the "Applies to" field. Tried entering "=COLUMN()>5" but it changes back to what it was.

Also, I don't know know much about volatility.

 

[Peter_SSs]

Also, I don't know know much about volatility.

That is why I made that word a hyperlink in my last post.
You can follow that link and read about volatility

I need to put something in the "Applies to" field.

OK, if you want to do it that way try these steps

• Select cell A1
• Home ribbon tab -> Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:- =COLUMN()>5 -> Format... -> Fill tab -> Choose a colour -> OK -> OK
• Home ribbon tab -> Conditional Formatting -> Manage rules... -> In the Applies to box replace =$A$1 with =$A$1:$Q$10 (don't make it a huge range yet)-> Apply -> OK

Once you have read about volatility and decided if it may be an issue in your sheet, you can change that applies to range to be whatever you want.

 

[Formula11]

OK thanks.
I found that this worked as well.

=AND(COLUMN()>5,RIGHT(FORMULATEXT(A1),2)="+2")

 

[Peter_SSs]

You're welcome.

I found that this worked as well.

=AND(COLUMN()>5,RIGHT(FORMULATEXT(A1),2)="+2")

That is for a different requirement but if your goal is to highlight only cells in column F or greater that also have a formula ending with "+2" then that should work fine.

 

[Formula11]

OK thanks for confirming.

 

[Peter_SSs]

Are you likely to be inserting or deleting column to the left of the cells you are checking?
That is, for the example you gave in post #5, are you likely to be inserting/deleting columns from A to E?
If not, then a simpler CF formula is possible. Just the RIGHT(FORMULATEXT part and only apply the CF to columns F onwards.

 

[Formula11]

No, not likely to insert or delete columns before.
But I couldn't make sense of how to apply from Nth column to end in the "Applies to" field.

I know with rows you can do something like below and it won't change if you enter or delete rows after the Nth.
=$7:$1048576

 

[Peter_SSs]

Remove the existing CF
Select F1 and apply this CF rule: =RIGHT(FORMULATEXT(F1),2)="+2"
Go back in to Manage rules and change Applies to to, say, =$F:$Z

If you really want all columns then use $XFD instead of $Z, but surely you are not using anything like 16,000 columns?