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


--

Reggie


--

Reggie

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
>
> --
>
> Reggie
>
> --
>
> Reggie

--

Dave Peterson

Dave, Thanks for the reply. Those are real numbers. I am pasting the
values directly from a recordset from MSAccess. I did the test you
suggested and yes the only remaining selected values were the numbers. Have
to go to my daughters play right now but will try the rest of your
suggestion when I get back. Thanks much. Will let you know how it turns
out.


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>>
>> --
>>
>> Reggie
>>
>> --
>>
>> Reggie
>
> --
>
> Dave Peterson



--

Reggie

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>>
>> --
>>
>> Reggie
>>
>> --
>>
>> Reggie
>
> --
>
> Dave Peterson

--

Reggie

I'd move all those dim's to the top of the procedure -- no reason to declare
them over and over in your loop.

And I'd use "As Double". It turns out that Doubles are more efficient than
Singles and that's how excel would retrieve the values from the worksheet
anyway.



JRSmith wrote:
>
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >>
> >> --
> >>
> >> Reggie
> >>
> >> --
> >>
> >> Reggie
> >
> > --
> >
> > Dave Peterson
>
> --
>
> Reggie

--

Dave Peterson

I'll do it. Got a little carried away. Thanks again.
"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'd move all those dim's to the top of the procedure -- no reason to
> declare
> them over and over in your loop.
>
> And I'd use "As Double". It turns out that Doubles are more efficient
> than
> Singles and that's how excel would retrieve the values from the worksheet
> anyway.
>
>
>
> JRSmith wrote:
>>
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >>
>> >> --
>> >>
>> >> Reggie
>> >>
>> >> --
>> >>
>> >> Reggie
>> >
>> > --
>> >
>> > Dave Peterson
>>
>> --
>>
>> Reggie
>
> --
>
> Dave Peterson

--

Reggie