MrExcel Publishing
Your One Stop for Excel Tips & Solutions

This really needs a clever person.


Posted by Natlie Greppi on December 20, 2001 10:56 AM

how can a macro be triggered by the entry of certain figures in a cell.
how it can do different things based on the entered data.
after the macro finish the function the input cell is cleared for the input of new figure.
how can I make a macro that sorts columns based on a certain criteria that is triggered by a certain condition.
several macros that work based on a letter in cell. Performs different things based on what is in the cell then clears the letter automically awaiting the input of another.


Posted by Joe Was on December 20, 2001 11:17 AM

This code will color a cell based upon the value in the cell, what you want is an extention of this, to your application. JSW

Sub ColorGet()
'
Dim vTest
Range("A1:F1").Select

'If value tests.
'Gray.
If Range("A1").Value = "" Or Range("B1").Value = "" Or _
Range("C1").Value = "" Or Range("D1").Value = "" Or _
Range("E1").Value = "" Or Range("F1").Value = "" Then
vTest = 3
End If

'Red.
If ((Range("A1").Value < 40 And Range("A1").Value <> 0)) Or _
((Range("B1").Value < 40 And Range("A1").Value <> 0)) Or _
((Range("C1").Value < 40 And Range("A1").Value <> 0)) Or _
((Range("D1").Value < 40 And Range("A1").Value <> 0)) Or _
((Range("E1").Value < 40 And Range("A1").Value <> 0)) Or _
((Range("F1").Value < 40 And Range("A1").Value <> 0)) Then
vTest = 1
End If

'Blue.
If Range("A1").Value >= 40 Or Range("B1").Value >= 40 Or _
Range("C1").Value >= 40 Or Range("D1").Value >= 40 Or _
Range("E1").Value >= 40 Or Range("F1").Value >= 40 Then
vTest = 2
End If

'Colors.
'Red.
If vTest = 1 Then
Selection.FormatConditions.Delete
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Else

'Blue.
If vTest = 2 Then
Selection.FormatConditions.Delete
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Else

'Gray.
If vTest = 3 Then
With Selection.Interior
Selection.FormatConditions.Delete
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End If
End If

Range("A1").Select
End Sub

Hope this helps, If not, post your code and we can fix it. JSW