My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Conditional formatting rules not working in filtered table|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Conditional formatting rules not working in filtered table|General Excel Questions & Answers|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope



Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_PrintTopic sp_TopicIcon
Conditional formatting rules not working in filtered table
Avatar
Bunmi Sanusi
Member
Members
Level 0
Forum Posts: 6
Member Since:
June 11, 2020
sp_UserOfflineSmall Offline
1
September 26, 2020 - 7:12 am
sp_Permalink sp_Print sp_EditHistory

Hello, I applied a conditional formatting for alternate rows for two specific columns in an excel table. The issue is that when it is filtered, the alternate rows do not show the formatting as alternating for the visible cells. Please assist. I have attached the file. Thanks

sp_AnswersTopicSeeAnswer See Answer
Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 414
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
September 26, 2020 - 1:49 pm
sp_Permalink sp_Print

It is working. The formula you have put in will colour the cells according to the row number - when you filter the row numbers dont change, you are just hiding the filter out rows

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 873
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
3
September 26, 2020 - 4:24 pm
sp_Permalink sp_Print

Hello,

Just replace the ROW() part in the formula with SUBTOTAL(3,$E$1:$E1), depending on which cell (row) you want to be highlighted you might need to change the last number also. See attached file for reference.

Br,
Anders

sp_AnswersTopicAnswer
Answers Post
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Louis Muti, Misael Gutierrez Sr.
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Joao Marques
Regi Hampton
Taffie Elliott
Paramita Chakraborty
David du Toit
Forum Stats:
Groups: 3
Forums: 24
Topics: 6358
Posts: 27805

 

Member Stats:
Guest Posters: 49
Members: 32335
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen