Windows Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed Forums > Operating Systems > Windows Help
Reload this Page

Excel Conditional Formatting Question


Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old January 10th, 2012, 12:39 PM
Nullified's Avatar
Nullified Nullified is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Dec 2003
Posts: 159 Nullified User rank is Sergeant Major (2000 - 5000 Reputation Level)Nullified User rank is Sergeant Major (2000 - 5000 Reputation Level)Nullified User rank is Sergeant Major (2000 - 5000 Reputation Level)Nullified User rank is Sergeant Major (2000 - 5000 Reputation Level)Nullified User rank is Sergeant Major (2000 - 5000 Reputation Level)Nullified User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 19 h 23 m 37 sec
Reputation Power: 30
Excel Conditional Formatting Question
I have a spreadsheet with the following conditional format rules (as formulas):
Code:
=(INDIRECT("G"&ROW())="Cancelled")     //Applies to $A:$G and Turns Row Red
=(INDIRECT("G"&ROW())="Hold")          //Applies to $A:$G and Turns Row Amber
=(INDIRECT("G"&ROW())="Complete")      //Applies to $A:$G and Turns Row Green

These work fine if the cell in Column G matches Cancelled, Hold or Complete, but I would like to change it so that if any cell in column G contains (not matches) those keywords anywhere in that cell that the whole row changes color. I have tried many variations of find/search/iserror with no luck. Please help
Last edited by Nullified : January 10th, 2012 at 12:43 PM.
Reply With Quote
  #2  
Old January 12th, 2012, 09:38 AM
AdamPI's Avatar
AdamPI AdamPI is offline
Automagically Delicious
Dev Shed Regular (2000 - 2499 posts)
  
Join Date: May 2004
Location: 127.0.0.2 - I live next door.
Posts: 2,198 AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Weeks 6 Days 22 h 13 m 56 sec
Reputation Power: 2735
Which version of Excel and are you using VBA script or just cell formulas?
__________________
Adam TT
Reply With Quote
  #3  
Old January 17th, 2012, 01:18 PM
medialint's Avatar
medialint medialint is offline
Type Cast Exception
Dev Shed God 20th Plane (14500 - 14999 posts)
  
Join Date: Apr 2004
Location: OAKLAND CA | Adam's Point (Fairyland)
Posts: 14,938 medialint User rank is General 112nd Grade (Above 100000 Reputation Level)medialint User rank is General 112nd Grade (Above 100000 Reputation Level)medialint User rank is General 112nd Grade (Above 100000 Reputation Level)medialint User rank is General 112nd Grade (Above 100000 Reputation Level)medialint User rank is General 112nd Grade (Above 100000 Reputation Level)medialint User rank is General 112nd Grade (Above 100000 Reputation Level)medialint User rank is General 112nd Grade (Above 100000 Reputation Level)medialint User rank is General 112nd Grade (Above 100000 Reputation Level)medialint User rank is General 112nd Grade (Above 100000 Reputation Level)medialint User rank is General 112nd Grade (Above 100000 Reputation Level)medialint User rank is General 112nd Grade (Above 100000 Reputation Level)medialint User rank is General 112nd Grade (Above 100000 Reputation Level)medialint User rank is General 112nd Grade (Above 100000 Reputation Level)medialint User rank is General 112nd Grade (Above 100000 Reputation Level)medialint User rank is General 112nd Grade (Above 100000 Reputation Level)medialint User rank is General 112nd Grade (Above 100000 Reputation Level)  Folding Points: 319635 Folding Title: Super Ultimate Folder - Level 1Folding Points: 319635 Folding Title: Super Ultimate Folder - Level 1Folding Points: 319635 Folding Title: Super Ultimate Folder - Level 1Folding Points: 319635 Folding Title: Super Ultimate Folder - Level 1Folding Points: 319635 Folding Title: Super Ultimate Folder - Level 1Folding Points: 319635 Folding Title: Super Ultimate Folder - Level 1
Time spent in forums: 6 Months 2 Weeks 2 Days 13 m 33 sec
Reputation Power: 8490
Facebook
I could easily tell you how to do this with VBA, but as far as doing it strictly with a worksheet formula I don't have any ideas.
__________________
medialint.com

“Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
Reply With Quote
  #4  
Old January 17th, 2012, 02:25 PM
Nullified's Avatar
Nullified Nullified is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Dec 2003
Posts: 159 Nullified User rank is Sergeant Major (2000 - 5000 Reputation Level)Nullified User rank is Sergeant Major (2000 - 5000 Reputation Level)Nullified User rank is Sergeant Major (2000 - 5000 Reputation Level)Nullified User rank is Sergeant Major (2000 - 5000 Reputation Level)Nullified User rank is Sergeant Major (2000 - 5000 Reputation Level)Nullified User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 19 h 23 m 37 sec
Reputation Power: 30
I am using Excel 2007. I was using just formulas, but I didn't even think about using visual basic. I guess I could do that, if you could give me a heads up on a proper macro function.
Reply With Quote
  #5  
Old January 18th, 2012, 09:01 AM
AdamPI's Avatar
AdamPI AdamPI is offline
Automagically Delicious
Dev Shed Regular (2000 - 2499 posts)
  
Join Date: May 2004
Location: 127.0.0.2 - I live next door.
Posts: 2,198 AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level)AdamPI User rank is General 26th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Weeks 6 Days 22 h 13 m 56 sec
Reputation Power: 2735
Code:
Sub ColorRow()

     If range("G3").value = find("hold") Then
        range("A3:F3").cells.interior.colorindex = 3
    Else if range("g3").value = find("complete") Then
        range("A3:F3").cells.interior.colorindex = 4
    End If
End Sub


Here is a great page for detailing the colors of the backgrounds and text and how to do it. Otherwise you can do something as simple as what I have or throw it into a loop. Google will be your best friend for many of those commands.
Reply With Quote
Reply

Viewing: Dev Shed Forums > Operating Systems > Windows Help > Excel Conditional Formatting Question

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap