Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / March 2007

Tip: Looking for answers? Try searching our database.

Conditional Formatting....

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Chris - 08 Mar 2007 19:57 GMT
I want to do more than three conditional formats....the condition is if the
word 'deliver' exists the color should be green....if the word 'tank set'
exists the color should be off white....I have about twenty words with twenty
different colors...any suggestions. Thanks Chris
Toppers - 08 Mar 2007 20:25 GMT
Try:

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10"    '<=== change to suit

   check_words = Array("word", "tank set")  '<=== set to your words/phrases
   On Error GoTo ws_exit:
   Application.EnableEvents = False
   If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
       With Target
           For i = LBound(check_words) To UBound(check_words)
           If InStr(1, .Value, check_words(i)) Then
               Select Case i + 1
                   Case 1: .Interior.ColorIndex = 3    'red
                   Case 2: .Interior.ColorIndex = 6    'yellow
                   Case 3: .Interior.ColorIndex = 5    'blue
                   Case 4: .Interior.ColorIndex = 10   'green
               End Select
           GoTo ws_exit
           End If
           Next i
           
           
       End With
   End If

ws_exit:
   Application.EnableEvents = True
End Sub

> I want to do more than three conditional formats....the condition is if the
> word 'deliver' exists the color should be green....if the word 'tank set'
> exists the color should be off white....I have about twenty words with twenty
> different colors...any suggestions. Thanks Chris
Gary''s Student - 08 Mar 2007 20:30 GMT
You should use a macro to paint the colors rather than Conditional Formatting.
Signature

Gary''s Student
gsnu200709

> I want to do more than three conditional formats....the condition is if the
> word 'deliver' exists the color should be green....if the word 'tank set'
> exists the color should be off white....I have about twenty words with twenty
> different colors...any suggestions. Thanks Chris
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.