Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Fill color

  Asked By: Jason    Date: Dec 21    Category: MS Office    Views: 891
  

I have a list of project numbers associated with dates
that is sorted by date. I have it set up so that in another worksheet
the project numbers are copied to the date that they were in use. I
would like each project to have a unique fill colour in the calendar.
Also in the list of projects sorted by date the projects can appear at
different times throughout the year. I was thinking I could copy the
contents of that worksheet into another, sort by project, then delete
duplicate projects, then assign a fill color number to each project.
Up to this point I think I would be OK but then I need to find each
occurence of each project in the calendar and fill the space in with
the corresponding colour. I am a bit of a noob when it comes to VBA in
excel but I am familiar with general programming concepts. Just
wondering if there is a builtin function in excel or a simple little
procedure to find all occurences of a text string in a worksheet so I
can fill the project numbers with their specified colour.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Rudy Turner     Answered On: Dec 21

If I understand, this may help get you started. It will color  all
cells containing "p1" yellow. This could be expanded to different
colors for different text. Brad

Sub ColorMe()
For Each c In ActiveSheet.UsedRange
If Contains("p1", c) Then
With c.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Else
With c.Interior
.ColorIndex = xlNone
.Pattern = xlNone
End With
End If
Next
End Sub

Function Contains(LookFor, LookIn)
On Error GoTo ErrorHandler
temp = Application.WorksheetFunction.Search(LookFor, LookIn)
'if no error
Contains = True
Exit Function
ErrorHandler:
Contains = False
End Function

 
Didn't find what you were looking for? Find more on Fill color Or get search suggestion and latest updates.




Tagged:    

 
 
 
 

Related Post