Repeat statements "x" number of times...

Hi All,

I have the following code at the end of a long macro:

    Columns("D:D").Find(What:="total").Select
    ActiveCell.Offset(0, 4).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
    ActiveCell.NumberFormat = "0.00"
    ActiveCell.Font.Bold = True
    With ActiveCell.Interior
        .ColorIndex = 6
    End With

What I am doing is when the word "total" appears, it will go to the cell 4 
columns to the right, and then do a formula and format the cell.

Question:  How can I get this to repeat itself for each time the word 
"total" appears.  Each different time I run this macro, I may have anywhere 
from 1 to 100 totals in the respective worksheet.  I think that I need to 
either loop, or do a for..next statement, but I don't know how to get the 
syntax right, or which would work better.

Any help would be greatly appreciated...thanks in advance...

Sara
0
6/24/2005 1:21:02 PM
excel.misc 78881 articles. 5 followers. Follow

14 Replies
482 Views

Similar Articles

[PageSpeed] 48

No need to repeat the statements, just need to find all the values first.

HTH,
Bernie
MS Excel MVP

Sub FindValues()
Dim c As Range
Dim d As Range
Dim firstAddress As String

'First, find all the cells
With Cells

   Set c = .Find("Total", LookIn:=xlValues, lookAt:=xlWhole)

   If Not c Is Nothing Then
      Set d = c
      firstAddress = c.Address
   Else:
      MsgBox "Not Found"
      End
   End If

   Set c = .FindNext(c)
   If Not c Is Nothing And c.Address <> firstAddress Then
      Do
         Set d = Union(d, c)
         Set c = .FindNext(c)
      Loop While Not c Is Nothing And c.Address <> firstAddress
   End If
End With

' Then do what you want with the offset of
' all the cells that have been found:
With d.Offset(0, 4)
   .FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
    .NumberFormat = "0.00"
    .Font.Bold = True
    With .Interior
        .ColorIndex = 6
    End With
End With

End Sub


"Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
message news:5DAB77D2-C864-4CD8-8A12-18DF1C2E0CC6@microsoft.com...
> Hi All,
>
> I have the following code at the end of a long macro:
>
>     Columns("D:D").Find(What:="total").Select
>     ActiveCell.Offset(0, 4).Range("A1").Select
>     ActiveCell.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
>     ActiveCell.NumberFormat = "0.00"
>     ActiveCell.Font.Bold = True
>     With ActiveCell.Interior
>         .ColorIndex = 6
>     End With
>
> What I am doing is when the word "total" appears, it will go to the cell 4
> columns to the right, and then do a formula and format the cell.
>
> Question:  How can I get this to repeat itself for each time the word
> "total" appears.  Each different time I run this macro, I may have
anywhere
> from 1 to 100 totals in the respective worksheet.  I think that I need to
> either loop, or do a for..next statement, but I don't know how to get the
> syntax right, or which would work better.
>
> Any help would be greatly appreciated...thanks in advance...
>
> Sara


0
Bernie
6/24/2005 2:59:28 PM
Bernie,

This isn't working...it is popping up the box and saying not found....

"Bernie Deitrick" wrote:

> No need to repeat the statements, just need to find all the values first.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> Sub FindValues()
> Dim c As Range
> Dim d As Range
> Dim firstAddress As String
> 
> 'First, find all the cells
> With Cells
> 
>    Set c = .Find("Total", LookIn:=xlValues, lookAt:=xlWhole)
> 
>    If Not c Is Nothing Then
>       Set d = c
>       firstAddress = c.Address
>    Else:
>       MsgBox "Not Found"
>       End
>    End If
> 
>    Set c = .FindNext(c)
>    If Not c Is Nothing And c.Address <> firstAddress Then
>       Do
>          Set d = Union(d, c)
>          Set c = .FindNext(c)
>       Loop While Not c Is Nothing And c.Address <> firstAddress
>    End If
> End With
> 
> ' Then do what you want with the offset of
> ' all the cells that have been found:
> With d.Offset(0, 4)
>    .FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
>     .NumberFormat = "0.00"
>     .Font.Bold = True
>     With .Interior
>         .ColorIndex = 6
>     End With
> End With
> 
> End Sub
> 
> 
> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> message news:5DAB77D2-C864-4CD8-8A12-18DF1C2E0CC6@microsoft.com...
> > Hi All,
> >
> > I have the following code at the end of a long macro:
> >
> >     Columns("D:D").Find(What:="total").Select
> >     ActiveCell.Offset(0, 4).Range("A1").Select
> >     ActiveCell.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
> >     ActiveCell.NumberFormat = "0.00"
> >     ActiveCell.Font.Bold = True
> >     With ActiveCell.Interior
> >         .ColorIndex = 6
> >     End With
> >
> > What I am doing is when the word "total" appears, it will go to the cell 4
> > columns to the right, and then do a formula and format the cell.
> >
> > Question:  How can I get this to repeat itself for each time the word
> > "total" appears.  Each different time I run this macro, I may have
> anywhere
> > from 1 to 100 totals in the respective worksheet.  I think that I need to
> > either loop, or do a for..next statement, but I don't know how to get the
> > syntax right, or which would work better.
> >
> > Any help would be greatly appreciated...thanks in advance...
> >
> > Sara
> 
> 
> 
0
6/24/2005 6:44:06 PM
Bernie...

I think I figured it out....I changed the .find....xlwhole to 
..find.....xlpart...because the totals have fiscal years in front of 
them...i.e. F05 total...


The only question I have now is I only want it to look in column D for this, 
not the whole document because there might be the word "total in other 
columns....

"Frantic Excel-er" wrote:

> Bernie,
> 
> This isn't working...it is popping up the box and saying not found....
> 
> "Bernie Deitrick" wrote:
> 
> > No need to repeat the statements, just need to find all the values first.
> > 
> > HTH,
> > Bernie
> > MS Excel MVP
> > 
> > Sub FindValues()
> > Dim c As Range
> > Dim d As Range
> > Dim firstAddress As String
> > 
> > 'First, find all the cells
> > With Cells
> > 
> >    Set c = .Find("Total", LookIn:=xlValues, lookAt:=xlWhole)
> > 
> >    If Not c Is Nothing Then
> >       Set d = c
> >       firstAddress = c.Address
> >    Else:
> >       MsgBox "Not Found"
> >       End
> >    End If
> > 
> >    Set c = .FindNext(c)
> >    If Not c Is Nothing And c.Address <> firstAddress Then
> >       Do
> >          Set d = Union(d, c)
> >          Set c = .FindNext(c)
> >       Loop While Not c Is Nothing And c.Address <> firstAddress
> >    End If
> > End With
> > 
> > ' Then do what you want with the offset of
> > ' all the cells that have been found:
> > With d.Offset(0, 4)
> >    .FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
> >     .NumberFormat = "0.00"
> >     .Font.Bold = True
> >     With .Interior
> >         .ColorIndex = 6
> >     End With
> > End With
> > 
> > End Sub
> > 
> > 
> > "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> > message news:5DAB77D2-C864-4CD8-8A12-18DF1C2E0CC6@microsoft.com...
> > > Hi All,
> > >
> > > I have the following code at the end of a long macro:
> > >
> > >     Columns("D:D").Find(What:="total").Select
> > >     ActiveCell.Offset(0, 4).Range("A1").Select
> > >     ActiveCell.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
> > >     ActiveCell.NumberFormat = "0.00"
> > >     ActiveCell.Font.Bold = True
> > >     With ActiveCell.Interior
> > >         .ColorIndex = 6
> > >     End With
> > >
> > > What I am doing is when the word "total" appears, it will go to the cell 4
> > > columns to the right, and then do a formula and format the cell.
> > >
> > > Question:  How can I get this to repeat itself for each time the word
> > > "total" appears.  Each different time I run this macro, I may have
> > anywhere
> > > from 1 to 100 totals in the respective worksheet.  I think that I need to
> > > either loop, or do a for..next statement, but I don't know how to get the
> > > syntax right, or which would work better.
> > >
> > > Any help would be greatly appreciated...thanks in advance...
> > >
> > > Sara
> > 
> > 
> > 
0
6/24/2005 6:55:02 PM
Change

With Cells

to

With Rand("D:D")


HTH,
Bernie
MS Excel MVP

> The only question I have now is I only want it to look in column D for
this,
> not the whole document because there might be the word "total in other
> columns....


0
Bernie
6/24/2005 7:12:55 PM
Yikes! How did that happen?

With Rand("D:D")

should have been

With Range("D:D")

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:uyRFoCPeFHA.3048@TK2MSFTNGP12.phx.gbl...
> Change
>
> With Cells
>
> to
>
> With Rand("D:D")
>
>
> HTH,
> Bernie
> MS Excel MVP
>
> > The only question I have now is I only want it to look in column D for
> this,
> > not the whole document because there might be the word "total in other
> > columns....
>
>


0
Bernie
6/24/2005 7:27:37 PM
Hi Bernie,

Thanks so much for all the help....The code works great....I can't stop 
re-running it just to see it work.....This is very helpful....and also, 
thanks for the tip on finding the word first, and then executing the 
formating steps.....I would have never even known you can do that.....

One more question........what would be a good direction to go in to learn 
more about visual basic?????  

"Bernie Deitrick" wrote:

> Yikes! How did that happen?
> 
> With Rand("D:D")
> 
> should have been
> 
> With Range("D:D")
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:uyRFoCPeFHA.3048@TK2MSFTNGP12.phx.gbl...
> > Change
> >
> > With Cells
> >
> > to
> >
> > With Rand("D:D")
> >
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > > The only question I have now is I only want it to look in column D for
> > this,
> > > not the whole document because there might be the word "total in other
> > > columns....
> >
> >
> 
> 
> 
0
6/27/2005 2:36:25 PM
Lurk here and read the posts and answers, and read any book in the VBA Power
Programming series by John Walkenbach.

HTH,
Bernie
MS Excel MVP


"Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
message news:4636F4E7-4373-4CED-971D-DD27702D9148@microsoft.com...
> Hi Bernie,
>
> Thanks so much for all the help....The code works great....I can't stop
> re-running it just to see it work.....This is very helpful....and also,
> thanks for the tip on finding the word first, and then executing the
> formating steps.....I would have never even known you can do that.....
>
> One more question........what would be a good direction to go in to learn
> more about visual basic?????


0
Bernie
6/27/2005 2:50:06 PM
Bernie,

I have some more formatting issues now that pertain to the code you wrote me 
in this thread.

I now need to find in column H...all the cells that have a formula response 
of #N/A, or "India Only", and I need to highlight those in red.  I tried to 
copy this code that formatted the other column for me, but it doesn't work - 
I get a duplicate compile error.  Any suggestions on how I can get these 
responses highlighted????

Thanks.

Sara

"Bernie Deitrick" wrote:

> Lurk here and read the posts and answers, and read any book in the VBA Power
> Programming series by John Walkenbach.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> message news:4636F4E7-4373-4CED-971D-DD27702D9148@microsoft.com...
> > Hi Bernie,
> >
> > Thanks so much for all the help....The code works great....I can't stop
> > re-running it just to see it work.....This is very helpful....and also,
> > thanks for the tip on finding the word first, and then executing the
> > formating steps.....I would have never even known you can do that.....
> >
> > One more question........what would be a good direction to go in to learn
> > more about visual basic?????
> 
> 
> 
0
6/29/2005 5:08:05 PM
Don't use code, use conditional formatting.  Look in help.

HTH,
Bernie
MS Excel MVP


"Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in message 
news:0BAAB0E3-1390-4E0A-9EE5-D3CCC126C212@microsoft.com...
> Bernie,
>
> I have some more formatting issues now that pertain to the code you wrote me
> in this thread.
>
> I now need to find in column H...all the cells that have a formula response
> of #N/A, or "India Only", and I need to highlight those in red.  I tried to
> copy this code that formatted the other column for me, but it doesn't work -
> I get a duplicate compile error.  Any suggestions on how I can get these
> responses highlighted????
>
> Thanks.
>
> Sara
>
> "Bernie Deitrick" wrote:
>
>> Lurk here and read the posts and answers, and read any book in the VBA Power
>> Programming series by John Walkenbach.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
>> message news:4636F4E7-4373-4CED-971D-DD27702D9148@microsoft.com...
>> > Hi Bernie,
>> >
>> > Thanks so much for all the help....The code works great....I can't stop
>> > re-running it just to see it work.....This is very helpful....and also,
>> > thanks for the tip on finding the word first, and then executing the
>> > formating steps.....I would have never even known you can do that.....
>> >
>> > One more question........what would be a good direction to go in to learn
>> > more about visual basic?????
>>
>>
>> 


0
Bernie
6/29/2005 5:20:27 PM
I tried to use conditional formatting, but it doesn't seem to know that I 
want it to look at a formula result....Also, this is the last bit of 
formatting that I need to do in order to finish a massive macro that I wrote. 
 I would like for it to be included in the macro.  I have recorded a macro 
while doing conditional format, and it looks like this:
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=$O$146"
    Selection.FormatConditions(1).Interior.ColorIndex = 3

the formulas in column H are vlookups with code that looks like this:
    With ActiveCell
        RowCount = .Offset(0, -1).End(xlDown).Row - .Offset(0, -1).Row + 1
        .FormulaR1C1 = _
        
"=IF(RC[-4]=""C"",VLOOKUP(RC[-3],USB,2,FALSE),VLOOKUP(RC[-3],FAS,2,FALSE))"
        .AutoFill .Resize(RowCount)
    End With

Not sure how to combine the two.



"Bernie Deitrick" wrote:

> Don't use code, use conditional formatting.  Look in help.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in message 
> news:0BAAB0E3-1390-4E0A-9EE5-D3CCC126C212@microsoft.com...
> > Bernie,
> >
> > I have some more formatting issues now that pertain to the code you wrote me
> > in this thread.
> >
> > I now need to find in column H...all the cells that have a formula response
> > of #N/A, or "India Only", and I need to highlight those in red.  I tried to
> > copy this code that formatted the other column for me, but it doesn't work -
> > I get a duplicate compile error.  Any suggestions on how I can get these
> > responses highlighted????
> >
> > Thanks.
> >
> > Sara
> >
> > "Bernie Deitrick" wrote:
> >
> >> Lurk here and read the posts and answers, and read any book in the VBA Power
> >> Programming series by John Walkenbach.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> >> message news:4636F4E7-4373-4CED-971D-DD27702D9148@microsoft.com...
> >> > Hi Bernie,
> >> >
> >> > Thanks so much for all the help....The code works great....I can't stop
> >> > re-running it just to see it work.....This is very helpful....and also,
> >> > thanks for the tip on finding the word first, and then executing the
> >> > formating steps.....I would have never even known you can do that.....
> >> >
> >> > One more question........what would be a good direction to go in to learn
> >> > more about visual basic?????
> >>
> >>
> >> 
> 
> 
> 
0
6/29/2005 5:36:04 PM
Okay.....I got it to format the "India Only" because that is an actual return 
from my vlookup...The "#N/A" I am having trouble with because that is 
referring to the account number not being on my vlookup table.  I have tried 
that several ways, and can't seem to figure that one out....

here is the code I have for the formatting of India only 
    Range("H:H").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""India Only"""
    Selection.FormatConditions(1).Interior.ColorIndex = 7


"Bernie Deitrick" wrote:

> Don't use code, use conditional formatting.  Look in help.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in message 
> news:0BAAB0E3-1390-4E0A-9EE5-D3CCC126C212@microsoft.com...
> > Bernie,
> >
> > I have some more formatting issues now that pertain to the code you wrote me
> > in this thread.
> >
> > I now need to find in column H...all the cells that have a formula response
> > of #N/A, or "India Only", and I need to highlight those in red.  I tried to
> > copy this code that formatted the other column for me, but it doesn't work -
> > I get a duplicate compile error.  Any suggestions on how I can get these
> > responses highlighted????
> >
> > Thanks.
> >
> > Sara
> >
> > "Bernie Deitrick" wrote:
> >
> >> Lurk here and read the posts and answers, and read any book in the VBA Power
> >> Programming series by John Walkenbach.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> >> message news:4636F4E7-4373-4CED-971D-DD27702D9148@microsoft.com...
> >> > Hi Bernie,
> >> >
> >> > Thanks so much for all the help....The code works great....I can't stop
> >> > re-running it just to see it work.....This is very helpful....and also,
> >> > thanks for the tip on finding the word first, and then executing the
> >> > formating steps.....I would have never even known you can do that.....
> >> >
> >> > One more question........what would be a good direction to go in to learn
> >> > more about visual basic?????
> >>
> >>
> >> 
> 
> 
> 
0
6/29/2005 6:02:03 PM
Bernie.....help......please......I am really trying hard......I have been at 
it for almost 4 hours........going crazy....

Can't figure out how to conditional format when the formula result is 
#N/A.....

"Bernie Deitrick" wrote:

> Don't use code, use conditional formatting.  Look in help.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in message 
> news:0BAAB0E3-1390-4E0A-9EE5-D3CCC126C212@microsoft.com...
> > Bernie,
> >
> > I have some more formatting issues now that pertain to the code you wrote me
> > in this thread.
> >
> > I now need to find in column H...all the cells that have a formula response
> > of #N/A, or "India Only", and I need to highlight those in red.  I tried to
> > copy this code that formatted the other column for me, but it doesn't work -
> > I get a duplicate compile error.  Any suggestions on how I can get these
> > responses highlighted????
> >
> > Thanks.
> >
> > Sara
> >
> > "Bernie Deitrick" wrote:
> >
> >> Lurk here and read the posts and answers, and read any book in the VBA Power
> >> Programming series by John Walkenbach.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> >> message news:4636F4E7-4373-4CED-971D-DD27702D9148@microsoft.com...
> >> > Hi Bernie,
> >> >
> >> > Thanks so much for all the help....The code works great....I can't stop
> >> > re-running it just to see it work.....This is very helpful....and also,
> >> > thanks for the tip on finding the word first, and then executing the
> >> > formating steps.....I would have never even known you can do that.....
> >> >
> >> > One more question........what would be a good direction to go in to learn
> >> > more about visual basic?????
> >>
> >>
> >> 
> 
> 
> 
0
6/29/2005 8:22:04 PM
Sorry, Frantic - I went on a short vacation...


    Range("H:H").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""India Only"""
    Selection.FormatConditions(1).Interior.ColorIndex = 7
    Selection.FormatConditions.Add Type:=xlExpression, 
Formula1:="=ISERROR(H1)"
    Selection.FormatConditions(2).Interior.ColorIndex = 7


HTH,
Bernie
MS Excel MVP


"Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in 
message news:94CDA8A0-E274-4B4F-B3E2-A8E33C2F205E@microsoft.com...
> Bernie.....help......please......I am really trying hard......I have been 
> at
> it for almost 4 hours........going crazy....
>
> Can't figure out how to conditional format when the formula result is
> #N/A.....
>
> "Bernie Deitrick" wrote:
>
>> Don't use code, use conditional formatting.  Look in help.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in 
>> message
>> news:0BAAB0E3-1390-4E0A-9EE5-D3CCC126C212@microsoft.com...
>> > Bernie,
>> >
>> > I have some more formatting issues now that pertain to the code you 
>> > wrote me
>> > in this thread.
>> >
>> > I now need to find in column H...all the cells that have a formula 
>> > response
>> > of #N/A, or "India Only", and I need to highlight those in red.  I 
>> > tried to
>> > copy this code that formatted the other column for me, but it doesn't 
>> > work -
>> > I get a duplicate compile error.  Any suggestions on how I can get 
>> > these
>> > responses highlighted????
>> >
>> > Thanks.
>> >
>> > Sara
>> >
>> > "Bernie Deitrick" wrote:
>> >
>> >> Lurk here and read the posts and answers, and read any book in the VBA 
>> >> Power
>> >> Programming series by John Walkenbach.
>> >>
>> >> HTH,
>> >> Bernie
>> >> MS Excel MVP
>> >>
>> >>
>> >> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
>> >> message news:4636F4E7-4373-4CED-971D-DD27702D9148@microsoft.com...
>> >> > Hi Bernie,
>> >> >
>> >> > Thanks so much for all the help....The code works great....I can't 
>> >> > stop
>> >> > re-running it just to see it work.....This is very helpful....and 
>> >> > also,
>> >> > thanks for the tip on finding the word first, and then executing the
>> >> > formating steps.....I would have never even known you can do 
>> >> > that.....
>> >> >
>> >> > One more question........what would be a good direction to go in to 
>> >> > learn
>> >> > more about visual basic?????
>> >>
>> >>
>> >>
>>
>>
>> 


0
Bernie
7/1/2005 2:30:17 AM
Thanks Bernie...

I finally figured it out the other day....

I felt so dumb....the whole time, the formatting wasn't working because I 
wasn't in the first cell of the row I was trying to fix....

I do have another question that would be great if you can help with...it is 
a separate thread "Macro - message box if find nothing"....Jim Rech was 
helping, but he decided I should be able to figure it out on my own, and I am 
stuck...If you don't mind reading thru, I would appreciate some input...

Thanks again...

Sara

"Bernie Deitrick" wrote:

> Sorry, Frantic - I went on a short vacation...
> 
> 
>     Range("H:H").Select
>     Selection.FormatConditions.Delete
>     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
>         Formula1:="=""India Only"""
>     Selection.FormatConditions(1).Interior.ColorIndex = 7
>     Selection.FormatConditions.Add Type:=xlExpression, 
> Formula1:="=ISERROR(H1)"
>     Selection.FormatConditions(2).Interior.ColorIndex = 7
> 
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in 
> message news:94CDA8A0-E274-4B4F-B3E2-A8E33C2F205E@microsoft.com...
> > Bernie.....help......please......I am really trying hard......I have been 
> > at
> > it for almost 4 hours........going crazy....
> >
> > Can't figure out how to conditional format when the formula result is
> > #N/A.....
> >
> > "Bernie Deitrick" wrote:
> >
> >> Don't use code, use conditional formatting.  Look in help.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in 
> >> message
> >> news:0BAAB0E3-1390-4E0A-9EE5-D3CCC126C212@microsoft.com...
> >> > Bernie,
> >> >
> >> > I have some more formatting issues now that pertain to the code you 
> >> > wrote me
> >> > in this thread.
> >> >
> >> > I now need to find in column H...all the cells that have a formula 
> >> > response
> >> > of #N/A, or "India Only", and I need to highlight those in red.  I 
> >> > tried to
> >> > copy this code that formatted the other column for me, but it doesn't 
> >> > work -
> >> > I get a duplicate compile error.  Any suggestions on how I can get 
> >> > these
> >> > responses highlighted????
> >> >
> >> > Thanks.
> >> >
> >> > Sara
> >> >
> >> > "Bernie Deitrick" wrote:
> >> >
> >> >> Lurk here and read the posts and answers, and read any book in the VBA 
> >> >> Power
> >> >> Programming series by John Walkenbach.
> >> >>
> >> >> HTH,
> >> >> Bernie
> >> >> MS Excel MVP
> >> >>
> >> >>
> >> >> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> >> >> message news:4636F4E7-4373-4CED-971D-DD27702D9148@microsoft.com...
> >> >> > Hi Bernie,
> >> >> >
> >> >> > Thanks so much for all the help....The code works great....I can't 
> >> >> > stop
> >> >> > re-running it just to see it work.....This is very helpful....and 
> >> >> > also,
> >> >> > thanks for the tip on finding the word first, and then executing the
> >> >> > formating steps.....I would have never even known you can do 
> >> >> > that.....
> >> >> >
> >> >> > One more question........what would be a good direction to go in to 
> >> >> > learn
> >> >> > more about visual basic?????
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
7/1/2005 3:19:04 PM
Reply:

Similar Artilces:

If statement with formatted text
Hi, Is there any way to have an if formula such as: If (A1="Active", "KAE",KPE") where the two letters after the K are formatted as subscript? The best I've done is to paste a picture over the cell. The picture's formula refers to named formula that selects one of two cells, the one with correct string. One cell contains KAE and the other KPE with the subscript. However, it means that I'm using a picture and it would be much nicer if I could just do it in an If formula. I hope that makes some kind of sense, and thanks in advance for your help. D...

How to print a graph of x=y at 45 degrees
How can I make it appear correctly on paper? Gordon, Set up two sets of points: 0 0 1, 1 Plot them with an x-y scatter chart, line style. Set the x and y axis scales the same, if necessary, to get the resulting plot to be at 45�. Print it, and hope for the best. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "gordon158" <gordon158@discussions.microsoft.com> wrote in message news:2C5B1255-334F-41A3-A5E5-55516CFABA6A@microsoft.com... > How can I make it appear correctly on paper? ...

active X control
I have a calandar (active x control) on my form. Does anyone know how to link/sync it to a bound control; i.e., when a user chooses a date control, the calandar appears, and when a date is selected it populates into the requisite field. alex Try this: http://www.datastrat.com/Download/XCalendar2K.zip -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "alex" <sql_aid@yahoo.com> wrote in message news:1182963470.109302.283780@q75g2000hsh.googlegroups.com... >I have a calandar (active x control) on my form. > Does anyo...

Extracting Time from a cell that has both the date and the time
Hi Folks, I could do with some help here please. I am trying to extract the time only from a cell that has both the date and the time. Can anyone suggest a solution? Thanks in advance. :confused: -- Hani Muhtadi ------------------------------------------------------------------------ Hani Muhtadi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26794 View this thread: http://www.excelforum.com/showthread.php?threadid=466177 If you just wish the time to display, set the cell format to Time. If you wish to use the time portion then =A1-Int(A1) will give you...

How do I sort data by date excluding time
I would like to sort a database by date and transaction type. The problem is the data sorts by time, even though the field is formatted to show date only, with the transaction type not sorting within the date because it shows up in the time order. How do I get rid of the time? If date/time column is A then you have to use an empty helper column filled with formula =INT(A2) and sort by it. -- Regards! Stefi „markd” ezt írta: > I would like to sort a database by date and transaction type. The problem is > the data sorts by time, even though the field is for...

IF(This=2 and That=1,X,Y)
Im comparing 2 values per. I need to evaluate if THIS = 1 and THAT = 2, then 1 otherwise 0 How do I do that in an expression? I always need to evaluate BOTH and they both need to be true. Any help would be appreciated. This is what I guessed, but doesn't work, surely there must be a method to evaluate both as the same time? IF R14 is 1 AND S14 is 1 then 7, otherwise 0 as: =IF(R14=1+S14=1,7,0) Use the AND() function: =IF(AND(This=1,That=2),1,0) "No Spam" <no_spam@eparisek.com> wrote in message news:8453884A-EBEC-44E8-9AD8-E7690F361AB0@microsoft.com... > Im...

Bar chart & Category (x) axis labels #2
If you make a bar chart in Excel 2003, and have two columns of text as Category (x) axis labels, the first column is shown vertical and the text in the second column is shown horizontal in the chart. If you open/make the same chart in Excel 97, the text in both columns is shown horizontal. If there is a lot of text, it becomes unreadable when it's vertical. Is this by design or can it be fixed somehow? ...

Time to text conversion
I have Mac's version of Office (Office 2004), but it shouldn't matter.. The problem: I have a column in a table containing times (for some events). Data found in the cells looks like this: 1) "20:00 2) "20:00/22:00" (starting/ending time) Excel has automatically formated type 1 cells as "time", and type 2 cells as "text". I need all the cells to be formatted as "text". How? If i change the formatting of a cell, it displays something like "0.833333" (20.00 divided by 24). The only solution I came out with is copying the whole co...

Re: too long execution time and access violation error
Oh ... and one more thing ... is CPlex one of your classes? If it is here's the problem ... CPlex::FreeDataChain() line 45 + 15 bytes It appears it's doing a delete that is causing the access violation. KS "KS" <ks@blah.com> wrote in message news:... > The reason why it's taking 55 seconds is because you're running the > application in debug. When running in debug, MFC has A LOT of overhead if > you're doing a lot of new's or a lot of delete's. This is a because MFC > does a lot of error checking to make sure you're not deletin...

Saving Excel files takes a huge amount of time
Hi, Excel takes 5-10 minutes whenever I try to save the file after making the smallest of changes, even if I add a value to just one cell. Excel is using 98 or 99% of the CPU during these 5-10 minutes. If I do a save as the file gets saved instantaneously. The save time for subsequent saves also decreased. Then it slowly started creeping up and now I am back at a situation where it takes 5-10 minutes The file size is 3MB with 2 tabs, lots of formulaes, also formulaes cross referencing the tabs. This is the raw data file, which I am having problem saving. There are 2 other excel files...

Trying to change x-axis date range
I have a scatter chart with dates on the x-axis and data on the y. When trying to change the range of my x-axis, I right click on the x-axis, click "format axis" and for "Minimum" and "Maximum" I click "Fixed" but it will only let me enter dates in the number format (i.e. 39983 instead of 6/19/2009). This is really annoying because I am constantly changing ranges to zoom in on the data and need to think about what the number format would be. I didn't have this problem with the earler version of excel (2003), but it started as soon as my offi...

CRM / SBT 6.x
Hello All & Greetings. I am looking for any material / Case Studies, etc. of Installing, Configuring & CUstomizing MS CRM and SBT Pro and would appreciate any material would be of immense help. thanks in advance. kishan I would go to www.greatplains.com and also microsoft.com/crm and have a dig around "R. Kishan Boddapati" <kishanb@sciencesinc.com> wrote in message news:11de01c3847e$e9a58370$a101280a@phx.gbl... > Hello All & Greetings. > > I am looking for any material / Case Studies, etc. of > Installing, Configuring & CUstomizing MS CRM and ...

excel charts to ppt: x-axis creep
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel The x-axis of large data-point line charts 'migrates' when copied to PPT; that is, the starting date in the x-axis creeps up from, say, 1973 to 1977 in charts copied to PPT. Any remedy for this? > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3346044558_20412597 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: 8bit On 1/8/10 6:20 PM, in article 59bb0604.-1@web...

Office v X Professional, Service Release 1
Can someone please tell me what updates I should install on SR-1? I am unable to tell if SR-1 is newer or older than 10.1.3. Thanks, Brent Hi Brent, See here for what you need, and how to find out. Sounds like you need 10.1.4 and 10.1.5. http://www.mcgimpsey.com/macoffice/office/vxversions.html DM "Brent Bosin" wrote: > Can someone please tell me what updates I should install on SR-1? I am > unable to tell if SR-1 is newer or older than 10.1.3. > > Thanks, > Brent > On 4/19/04 1:55 AM, in article BCA8E218.176%bbosin@sbcglobal.net, "Brent Bosin&quo...

why do mailbox limits take time to come into effect?
Hello, If i change someones limits, they cannot imediatly send messages (it still says that they have exceeded their limit). why does this take approx 1 hr? The user is connected to the exchange server (1 of 1). It should not need time to replicate, because it should not have anything to do with AD, DC, GC. What am i missing? thanks A This sounds like http://support.microsoft.com/default.aspx?scid=kb;en-us;327378&sd=RMVP "aaron.whittaker" <aaronwhittaker2002@yahoo.com> wrote in message news:2c874ed1.0501241531.2809889b@posting.google.com... > Hello, > If i chan...

excel numbering #3
icestationzbra Wrote: > someone had asked a similar question, someone had posted a resolution, > am just a carrier! > > Private Sub Workbook_Open() > Const DEFAULTSTART As Integer = 1 > Const MYAPPLICATION As String = "Excel" > Const MYSECTION As String = "myInvoice" > Const MYKEY As String = "myInvoiceKey" > Const MYLOCATION As String = "A1" > Dim regValue As Long > > With ThisWorkbook.Sheets(1).Range(MYLOCATION) > If .Text <> "" Then Exit Sub > regValue = GetSetting(MYAPPLICATION, MYSECTION,...

Office X 10.1.2 to 10.1.5 updates
Each time that I'm trying to install these updates, I get the error 11002:2'-15. Nothing is running even my anti-virus. Does somebody have an answer. Thank you On Mon, 23 Feb 2004 21:51:05 -0500, Doggy wrote (in message <013f01c3fa81$0c225e50$a401280a@phx.gbl>): > Each time that I'm trying to install these updates, I get > the error 11002:2'-15. Nothing is running even my anti-virus. > > Does somebody have an answer. > > Thank you It's been said, and it seems to have worked for me, that you need to have all internet access disabled. Tim M...

excel graph is only showing numbers not words-how do I change?
I have a chart that has a part number for the x axis. The chart area then shows the quantity of scrap and the next line shows reason for non-conformance. This chart will not recognize the reason for the non-confrormance. This is indicating "0" for them. How do I get them to indicate the actual words? ...

printing one lable at a time on sheet with six
I have a custom lable in publisher, I want to be able to print one only on a page of six at a time, like I can with Word lables. or import this pub lable into words lables list where I can print one on the page Start with a new work area and set the columns and rows to mimic the sheet of labels. Create a single label in the location that would use the label in the sheet. Put sheet of labels in the printer, Print. -- Don - Publisher 2000 Vancouver, USA "RMC" <RMC@discussions.microsoft.com> wrote in message news:E310E176-1351-40AF-B43B-2B6034F4B7D8@microsoft.com... >...

Office v. X program unexpectedly quits after doube-click
Hello, After I double click on an Office v.X program, expect Entourage, the splash page appears, reads font menu, then quits. I already checked out the Mactopia database and its font issues posting. I am running on OSX 10.2.8, G4 1.25 GHz 256 RAM. I have tried the following: 1. Compared duplicate fonts between User fonts and Library fonts (not the System fonts folder) 2. Updated all Office software to v. 10.1.5 3. Reinstalled Office 4. Removed Microsoft Prefernces from User folder, and allowed them to rebuild themselves If ANYONE has ANY info or has experienced this before, please pass on t...

URGENT ** x & z report display zero
I attached printers and cash drawers eposon tm-88iv printeres and generic cashdreawers Now my X and Z reports dsiplay zero totals I have read everything I can find to no avail, can anyone suggest a solution everythign worked fine before i attached the printers and cash drawers -- Thanks Ian "Ian Yorke" wrote: > I attached printers and cash drawers eposon tm-88iv printeres and generic > cashdreawers > > Now my X and Z reports dsiplay zero totals > > I have read everything I can find to no avail, can anyone suggest a solution > > everythign worke...

Red x's in All Outlook E-Mails
Suddenly from late last week (probably after last Tuesday's MS security updates) I am seeing Red x's in HTML messages. There is now no info bar to allow me to "allow" the contents to be displayed. It is not the normal settings within Outlook to display pictures. Something changed. Outlook 2007 on Vista Ultimate. Today put Office SP1 in and did not correct this fault. Even with the "sender in my trust list", still it doesn't display the missing parts Help appreciated Frenchy Frenchy wrote: > Suddenly from late last week (probably after last Tuesda...

How to format text and numbers as custom
How do you format a drivers license number? For example: Hxxx-xxx-xx-xxx-x, where x represents any number, but "H" represents any text? Do you have to use a macro? If so, does anyone have the code for this sequence? If always H then custom format of "H"&000-etc should do it or format as text and type in the - - - - or a worksheet change event to convert based on any entered letter and numbers entered witout - - - -- Don Guillett SalesAid Software donaldb@281.com "Julian Ganoudis" <Julian Ganoudis@discussions.microsoft.com> wrote in message news:198...

Excel formual IF statement Help
I am trying to make this IF statement work =IF(A4="",0,IF((A4="F"),B4,IF((A4="D"),B4,IF((A4="T"),B4,IF((A4="S"),C4,IF( (A4="M"),C4,IF((A4="L"),C4,IF((A4="H"),C4,IF((A4="G"),C4,IF((A4="C"),C4,"ERR") ))))))) On Sat, 01 May 2010 20:11:13 GMT, "Cerealkiller" <u59737@uwe> wrote: >I am trying to make this IF statement work > >=IF(A4="",0,IF((A4="F"),B4,IF((A4="D"),B4,IF((A4="T"),B4,IF((A4="S"),C4,IF( ...

store.exe consuming 99% of processor time
Our Exchange 5.5 box had a virus, which was quarantined and removed by Symantec AV. I had to delete and recreate the edb.chk file and also run isinteg -patch. It allowed me to start the Information Store and Internet Mail services. Unfortunately, the store service is consuming 99% of the processor's time. Is this normal? If not, what is happening? Thanks in advance, Craig Buchanan "Craig Buchanan" <someone@somewhere.com> wrote: >Our Exchange 5.5 box had a virus, which was quarantined and removed by >Symantec AV. I had to delete and recreate the edb.chk file...