• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Using conditional formatting with IF|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Using conditional formatting with IF|General Excel Questions & Answers|Excel Forum|My Online Training Hub
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_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Using conditional formatting with I…
sp_PrintTopic sp_TopicIcon
Using conditional formatting with IF
Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
1
May 24, 2019 - 10:17 pm
sp_Permalink sp_Print

Hi there,

I am trying to figure out how I can set the shading of a cell using conditional formatting based on the value of another cell.  For example if I have the word "Lemon" in cell b2 how can I tell Excel to change the color of cell b3 to yellow?

Any help appreciated!

Alan 

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
May 25, 2019 - 9:18 am
sp_Permalink sp_Print

Hi Alan

Please refer attachment.

Sunny

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 871
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
3
May 25, 2019 - 3:26 pm
sp_Permalink sp_Print

Hello Alan,

Just providing a link to some more information about conditional formatting using formulas.

https://www.myonlinetraininghu.....h-formulas

Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
4
May 28, 2019 - 11:29 pm
sp_Permalink sp_Print

SunnyKow/Anders,

Many thanks for the help - that's a good start for me.  I am going to try an incorporate some "If's" into the formula.  Will let you know if I am struggling!

Alan 

Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
5
May 30, 2019 - 10:35 pm
sp_Permalink sp_Print

Hi there,

I did say I would post again if I was struggling and I am 🙂 

In the attached file you will see that I am trying to generate a gantt style work sheet.  The date and durations entered in columns T and U define which cells are shaded under columns W onward. I am trying to figure out how I can change color of those shaded cells to a unique color based on the value selected in column H (Project).  So if the user selects "IOL D40" the shaded cells from BD-BZ will be say purple, if the user selects "Shell-Groundbirch ISM" from column H those same cells will change from purple to say orange etc.

Can anyone help me with this?

Thanks in advance,

Alan

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 871
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
6
June 1, 2019 - 10:52 pm
sp_Permalink sp_Print

Hello,

That is a lot of conditional formatting rules you have in your workbook. It seems to me that you know how to set up such rule you want help with. You have one such rule already, setting red background when =$H$2="IOL D40". That rule is applied to following ranges, =$LQ$3:$NN$3;$W$2:$NN$2;$OB$19:$SH$19;$NY$20:$SH$35;$NY$2:$SH$18;$W$4:$NN$5;$LS$36:$SH$68;$LS$6:$NN$35, which seems very spread out in my opinion.

I am really not sure what it is you want help with.

Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
7
June 3, 2019 - 10:14 pm
sp_Permalink sp_Print

Thanks Anders,

I apologize for my request being a bit confusing, I guess I was hoping there might be an "easier" way to get the effect I am after without having to enter so many conditions.  The project entries I would be using in the conditional rules will change over time, which would mean me having to amend the conditional formulas to reflect new projects getting added and older ones dropping off.  All of that is possible of course, just a bit long winded.

Thanks for taking the time to look at the problem and give me your feedback, sometimes its just as good to know there isn't a "magic bullet".  I'll carry on with the current approach and see just how much it turns out to be to maintain.

Alan

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Brian Pham, Riny van Eekelen
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
stuart burge
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Forum Stats:
Groups: 3
Forums: 24
Topics: 6223
Posts: 27295

 

Member Stats:
Guest Posters: 49
Members: 31920
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.