Conditional format - Nth column to End

Formula11

Active Member
Joined
Mar 1, 2005
Messages
461
Office Version
  1. 365
Platform
  1. Windows
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.

1643688614256.png
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
ABCDEFGHIJKLMNOPQ
1
2
3
4
CF Columns
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:Q16Expression=COLUMN()>5textNO
 
Upvote 0
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.


1643689369869.png
 
Upvote 0
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.
 
Upvote 0
OK thanks.
I found that this worked as well.

VBA Code:
=AND(COLUMN()>5,RIGHT(FORMULATEXT(A1),2)="+2")
 
Upvote 0
You're welcome.

I found that this worked as well.

VBA Code:
=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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Solution

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top