Dave, Your the man. Worked like a charm. Can't thank you enough. I'm
an
access geek so this is a little new. This is what I ended up with.
Basicly
just added some variables and did my logic on them. Refrencing all them
cells and offsets blah confuses me which aint to hard to do. Getting the
hang of it though. Take care.
Sub RBA_Cond_Form()
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet
Set wks = Worksheets("Sheet1")
With wks
Set myRng = Nothing
On Error Resume Next
Set myRng _
= .Columns(10).Cells.SpecialCells(xlCellTypeConstants,
xlNumbers)
On Error GoTo 0
If myRng Is Nothing Then
MsgBox "No numbers in column A"
Exit Sub
End If
For Each myCell In myRng.Cells
With myCell
Dim lngRBASTD As Single
Dim lngRBA As Single
Dim lngRBAFCF As Single
Dim lngRBASTDUpper As Single
Dim lngRBASTDLower As Single
lngRBASTD = .Offset(0, 1).Value
lngRBA = .Offset(0, 2).Value
lngRBAFCF = .Offset(0, 4).Value
lngRBASTDUpper = Abs((.Offset(0, 1).Value) - 0.01)
lngRBASTDLower = Abs((.Offset(0, 1).Value) - 0.99)
Select Case .Value 'FL Value
Case Is >= 3
If lngRBA >= lngRBASTD Then
.Offset(0, 2).Interior.ColorIndex = 10
.Offset(0, 2).Font.ColorIndex = 2
ElseIf (lngRBA >= lngRBASTDLower) And (lngRBA <=
lngRBASTDUpper) Then
.Offset(0, 2).Interior.ColorIndex = 6
.Offset(0, 2).Font.ColorIndex = 1
ElseIf lngRBA < (lngRBASTD - 1) Then
.Offset(0, 2).Interior.ColorIndex = 3
.Offset(0, 2).Font.ColorIndex = 2
End If
Case Is = 2
Select Case lngRBA
Case Is = 2
.Offset(0, 2).Interior.ColorIndex = 10
.Offset(0, 2).Font.ColorIndex = 2
Case Is = 1, 0
.Offset(0, 2).Interior.ColorIndex = 3
.Offset(0, 2).Font.ColorIndex = 2
End Select
Case Is = 1
Select Case lngRBA
Case Is = 1
.Offset(0, 2).Interior.ColorIndex = 10
.Offset(0, 2).Font.ColorIndex = 2
Case Is = 0
.Offset(0, 2).Interior.ColorIndex = 3
.Offset(0, 2).Font.ColorIndex = 2
End Select
End Select
End With
Next myCell
End With
End Sub
Dave Peterson said:
Are those really numbers in column A?
Are they constants or the results of numbers?
If they're really numbers and constants, then try this:
Select column A
Edit|goto (or hit F5 or ctrl-g)
click Special
Constants|Numbers (uncheck the other stuff)
and you should see just the rows to be inspected in the new selection.
if that worked ok, you can do the same thing in code:
Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet
Set wks = Worksheets("Sheet3")
With wks
Set myRng = Nothing
On Error Resume Next
Set myRng _
= .Columns(1).Cells.SpecialCells(xlCellTypeConstants,
xlNumbers)
On Error GoTo 0
If myRng Is Nothing Then
MsgBox "No numbers in column A"
Exit Sub
End If
For Each myCell In myRng.Cells
With myCell
Select Case .Value
Case Is >= 3
If .Offset(0, 2).Value > .Offset(0, 1).Value
Then
'do formatting here
ElseIf Abs(.Offset(0, 2).Value - .Offset(0,
1).Value) _
< 0.01 Then
'do formatting here
ElseIf .Offset(0, 2).Value _
< .Offset(0, 1).Value - 1 Then
'do formatting here
End If
Case Is = 2
Select Case .Offset(0, 2).Value
Case Is = 2
'do formatting here
Case Is = 1, 0
'do formatting here
End Select
Case Is = 1
Select Case .Offset(0, 2).Value
Case Is = 1
'do formatting here
Case Is = 0
'do formatting here
End Select
End Select
End With
Next myCell
End With
End Sub
If those numbers are the results of formulas, then you could use:
.Columns(1).Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
instead of:
.Columns(1).Cells.SpecialCells(xlCellTypeFormulas, xlNumbers)
And there are lots of shades of colors. I figured you could record a
macro to
get the colorindex numbers you want for each category.
JRSmith wrote:
Hi and TIA. I have a worksheet like so. I'm trying to conditionally
format
the cells in Column C. If this is possible where do a place a call to
the
procedure? I want the procedure to run for each individual row except
I
can't simply copy the formula down the sheet because I have headers
and
totals rows. I'm new to excel. I'm an Access geek. Any advice or if
you
can point me in the right direction is appreciated. Thanks for your
time!
Header: USS Vinson
A B C
1 3 3 1
2 2 3 4
3 1 2 2
Tot 6 8 7
Header: USS Eisenhower
A B C
1 3 3 1
2 2 3 4
3 1 2 2
Tot 6 8 7
Header: Marine Detachment
A B C
1 3 3 1
2 2 3 4
3 1 2 2
Tot 6 8 7
Select Case [A1]
Case >= 3
If [C1] >= [B1], background is Green with White fonts
If [C1] between [B1] - .01] and [B1] -.99], background is
Yellow
with Black Fonts
If [C1] is less than [B1]- 1], background is Red with White
fonts
Case = 2
If [C1] = 2, background is Green with White fonts
If [C1] = 1 or [C1] = 0, background is Red with White fonts
Case = 1
If [C1] = 1, background is Green with White fonts
If [C1] = 0, background is Red with White fonts
End select
