Changing Color in a Cell

50 pts.
Tags:
Conditional formatting
Excel 2003
Excel macros
Microsoft Excel
VBA
Visual Basic for Applications
I am updating a spreadsheet each month which is automated. I have written a macro to update this spread. Each month I append four columns to the end of the spread sheet and update the data in the spreadsheet. The problem that I am having is trying to figure out how to update the heading color for the 4 appended rows. The heading go across all four columns. I need the color to change each month based on the cell value in the heading. The value in the heading varies each month, ie April FY08, I want to evaluate the first three characters to determine the color of the cell. Below is a snippet of my code. Can someone please help me? Thank you.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Sorry I forgot to add my snippet of code:
myStr = Mid(Cells(5, intEndCols + 1), 1, 3).Value
Range(Cells(5, intEndCols + 1), Cells(5, intEndCols + 4)).Select
If ActiveCell.Value(myStr) = “May” Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = vbYellow
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Font.Bold = False
Selection.Font.Italic = True
Else
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = vbGreen
End With
End If

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BrentSheets
    This answer on conditional formatting may get you started in the right direction.
    6,925 pointsBadges:
    report
  • Kioh46
    I am not sure how to apply conditional formatting to my code because the month will change each time the spreadsheet is updated. The color of the Heading will depend on the month
    50 pointsBadges:
    report
  • BrentSheets
    I'm no whiz at programmatically using conditional formatting for Excel, but if every similar month is the same color (In other words, if "April FY08" will be the same color as "April FY09") - then couldn't you use a case statement to set up colors for 12 months?
    6,925 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following