As I stated in my previous post, my VBA skills are
rudimentary but I ordered J. Walkenbach's "VBA for Excel"
and I am hoping to learn a little more over the next few
weeks.
Meanwhile, can anyone point me in the right direction
with the VBA code needed to color cells a certain color,
depending on the corresponding cell content of another
worksheet:
Namely;
1) Worksheet 1 has a matrix with years running across the
top and product names running down column A.
2) Intersecting cells contain four (4) key words: EX, FS,
GE, or RE
3) the same matrix is copied over to a new worksheet 2
but, instead of keywords, I now simply want to shade the
cells different colors, depending on the keywords
contained in the parent sheet: maroon if cell content
is "EX," red if cell content is "GE," blue if cell
content is "RE" and green if cell content is "FS."
The intent is to create a worksheet that looks like a
timeline chart
Sub ChangeColor()
Dim Cell As Range
Dim RngToCheck As Range
With Sheets("Second")
Set RngToCheck = .Range("A1", .Range(.UsedRange.Address))
End With
For Each Cell In RngToCheck
With Sheets("Parent")
Select Case .Range(Cell.Address).Value
Case "EX"
Cell.Interior.Color = 18
Case "FS"
Cell.Interior.Color = vbGreen
Case "GE"
Cell.Interior.Color = vbRed
Case "RE"
Cell.Interior.Color = vbBlue
Case Else
Cell.Interior.Color = vbWhite
End Select
End With
Next Cell
End Sub
Sub CheckColorIndex()
'by Wilson -- creates colour chart on worksheet
Dim CurrVal As Integer
Dim ColorChart As Range
Dim Cell As Range
Set ColorChart = Range("A1:G8")
CurrVal = 1
For Each Cell In ColorChart
Cell.Value = CurrVal
Cell.Select
Selection.Font.Size = 14
Selection.Font.Bold = True
Selection.Font.ColorIndex = 2
Selection.Interior.ColorIndex = Cell.Value
Selection.Interior.Pattern = xlSolid
CurrVal = CurrVal + 1
Next Cell
End Sub
"K. Georgiadis" <anon...@discussions.microsoft.com> wrote in message
news:1489201c4450d$38d26360$a301...@phx.gbl...
'' GET COLOR CODES FROM FORMAT RANGE
Set rFormats = Worksheets("Data").Range("Formats")
Imax = rFormats.rows.count
Redim lFormats(1 to Imax,3)
For I = 1 To Imax
lFormats(I, 1) = rFormats.Cells(I, 1).Interior.ColorIndex
lFormats(I, 2) = rFormats.Cells(I, 1).Font.ColorIndex
lFormats(I, 3) = rFormats.Cells(I, 1).Value
Next
'' APPLY COLOR FORMATS TO WORKING RANGE
Set rWorking = Worksheets("Output").Range("Working")
For Each cell In rWorking
For I = 1 to Imax
If cell.value = lFormats(I, 3) Then
cell.Interior.ColorIndex = lFormats(I, 1)
cell.Font.ColorIndex = lFormats(I, 2)
End If
Next
Next
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______