Automating the rest of this....
I am new to Visual Basic programming in Excel. I am attempting to
program something that takes two numerical values out of Excel and
applies a color label such as "RED" or "YELLOW" above them depending
on their individual values. Then, a private sub-routine looks for
those word labels (like "RED) and colors them the appropriate color
automatically. The last thing I need to do is automate the word label
routine so that when I put a number in a cell and another number in
the cell right below it it applies the color label above those two
numbers. Any suggestions?
Thanks,
MZ
Example of format:
--------
|YELLOW|
--------
|2 |
--------
|2 |
--------
Here are some samples of my code:
Sub Worksheet_Change1()
'Define two variables
Dim LL As Integer
Dim ML As Integer
' begin error trapping.
On Error GoTo Done
' Start for loops to search through cells for numerical values
For i = 1 To 250
For j = 1 To 100
'take in a value at the ith and jth position
ML = Cells(i, j).Value
'take the value below the ith and jth cell
LL = Cells(i + 1, j).Value
'as long as ML is not zero start the conditional statements
If (Not ML) Then
'This is an example of how I assign color values to numerical
values, the rest of the code looks like this except for incrementing
the for loop
If (ML 0 And ML <= 5) Then
If (LL 0 And LL <= 6) Then
Cells(i - 1, j) = "GREEN"
ElseIf (LL 6 And LL <= 13) Then
Cells(i - 1, j) = "RED"
ElseIf (LL 13 And LL <= 25) Then
Cells(i - 1, j) = "YELLOW"
Else
End If
End If
Routine that automatically colors a RED, GREEN, or YELLOW label:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Begin looking through cells 100 accross and 250 down
For k = 1 To 250
For l = 1 To 100
'in the kth and lth cell make the target value the proper color
If ((Target.Row = k) And (Target.Column = l)) Then
If (Target.Value = "RED") Then
Target.Font.Color = vbRed
ElseIf (Target.Value = "GREEN") Then
Target.Font.Color = vbGreen
ElseIf (Target.Value = "YELLOW") Then
Target.Font.Color = vbYellow
Else
Target.Font.Color = vbDefault
End If
End If
Next l
Next k
End Sub
|