alienscript
07-01-2008, 12:56 AM
Hello VB expert,
I need some help please. I want to run thru each sh except Sheets("summary"), and in the InputBox I would type column "H". For every Cells(i, action).Value = "Accuracy" and then if the values in Cells(i, intCol) besides the cells "Accuracy" are having these criteria:
< -10% :then fill Interior.ColorIndex = 3 in Cells(i, intCol)
> +10% :then fill Interior.ColorIndex = 6 in Cells(i, intCol)
between -10% to 10% :then fill Interior.ColorIndex = 4 in Cells(i, intCol)
My syntax doesnt work. Could someone help me to amend this please. Many many thanks.
Option Explicit
Sub CellsColorFill()
Dim intCol As Integer, strCol As String, intColsInSheet As Integer, lastrow As Long
Dim action As Integer, measure As Single, accuracy As String, sh As Worksheet, i As Long
Sheets(3).Activate
Get_Column:
strCol = InputBox("type in the column or cell of the month that you want to measure and click OK")
If strCol = "" Then
MsgBox "Cancelled by User"
GoTo Bye
End If
intCol = Columns(strCol).Column ' convert column entered to integer
intColsInSheet = Cells(4, 256).End(xlToLeft).Column ' Get columns in the sheet
If intCol > intColsInSheet Then
MsgBox "you entered an invalid column. Please try again"
GoTo Get_Column
End If
For Each sh In Worksheets
If sh.Name Like "FEP*" = True And sh.Name Like "CMP" = True And sh.Name Like "*CVD" = True And sh.Name Like "*PVD" = True Then
lastrow = Sheets("CMP").Range("G65536").End(xlUp).Row
For i = 5 To lastrow
Rows("4:4").Cells.Find(What:="Action", After:=[A4]).Select
action = ActiveCell.Column
accuracy = Cells(i, action).Value
measure = Cells(i, intCol).Value
On Error Resume Next
If accuracy = "Accuracy" And measure <> vbNullString Then
Cells(i, intCol).Select
With Selection.Interior
If measure < -0.1 Then
.ColorIndex = 3
ElseIf measure > 0.1 Then
.ColorIndex = 6
ElseIf measure >= -0.1 And measure <= 0.1 Then
.ColorIndex = 4
End If
End With
End If
Next i
End If
Next sh
Bye: Exit Sub
End Sub
I need some help please. I want to run thru each sh except Sheets("summary"), and in the InputBox I would type column "H". For every Cells(i, action).Value = "Accuracy" and then if the values in Cells(i, intCol) besides the cells "Accuracy" are having these criteria:
< -10% :then fill Interior.ColorIndex = 3 in Cells(i, intCol)
> +10% :then fill Interior.ColorIndex = 6 in Cells(i, intCol)
between -10% to 10% :then fill Interior.ColorIndex = 4 in Cells(i, intCol)
My syntax doesnt work. Could someone help me to amend this please. Many many thanks.
Option Explicit
Sub CellsColorFill()
Dim intCol As Integer, strCol As String, intColsInSheet As Integer, lastrow As Long
Dim action As Integer, measure As Single, accuracy As String, sh As Worksheet, i As Long
Sheets(3).Activate
Get_Column:
strCol = InputBox("type in the column or cell of the month that you want to measure and click OK")
If strCol = "" Then
MsgBox "Cancelled by User"
GoTo Bye
End If
intCol = Columns(strCol).Column ' convert column entered to integer
intColsInSheet = Cells(4, 256).End(xlToLeft).Column ' Get columns in the sheet
If intCol > intColsInSheet Then
MsgBox "you entered an invalid column. Please try again"
GoTo Get_Column
End If
For Each sh In Worksheets
If sh.Name Like "FEP*" = True And sh.Name Like "CMP" = True And sh.Name Like "*CVD" = True And sh.Name Like "*PVD" = True Then
lastrow = Sheets("CMP").Range("G65536").End(xlUp).Row
For i = 5 To lastrow
Rows("4:4").Cells.Find(What:="Action", After:=[A4]).Select
action = ActiveCell.Column
accuracy = Cells(i, action).Value
measure = Cells(i, intCol).Value
On Error Resume Next
If accuracy = "Accuracy" And measure <> vbNullString Then
Cells(i, intCol).Select
With Selection.Interior
If measure < -0.1 Then
.ColorIndex = 3
ElseIf measure > 0.1 Then
.ColorIndex = 6
ElseIf measure >= -0.1 And measure <= 0.1 Then
.ColorIndex = 4
End If
End With
End If
Next i
End If
Next sh
Bye: Exit Sub
End Sub