colour cells using VBA

Hi

New to VBA so apologies in advance if a dumb question!

I have managed to get to the following which is working well but I now want 
corresponding cells in the "k" column to change to specific colours based on 
the data in "I" and "J" columns and seem to have put myself in a corner - 
what os the best way to write such a request please?
   
    'Convert to FMECA format, and set matrix row
    If consequence = "A - Almost Certain" Or LCase(consequence) = "a" Or 
LCase(consequence) = "a" Or LCase(consequence) = "almost certain" Then
        Range("i" & iRow).Value = "A - Almost Certain"
        iconsequence = 1
    ElseIf consequence = "B - Likely" Or LCase(consequence) = "b" Or 
LCase(consequence) = "l" Or LCase(consequence) = "likely" Then
        Range("i" & iRow).Value = "B - Likely"
        iconsequence = 2
    ElseIf consequence = "C - Possible" Or LCase(consequence) = "c" Or 
LCase(consequence) = "p" Or LCase(consequence) = "possible" Then
        Range("i" & iRow).Value = "C - Possible"
        iconsequence = 3
    ElseIf consequence = "D - Unlikely" Or LCase(consequence) = "d" Or 
LCase(consequence) = "u" Or LCase(consequence) = "unlikely" Then
        Range("i" & iRow).Value = "D - Unlikely"
        iconsequence = 4
    ElseIf consequence = "E - Rare" Or LCase(consequence) = "e" Or 
LCase(consequence) = "r" Or LCase(consequence) = "rare" Then
        Range("i" & iRow).Value = "E - Rare"
        iconsequence = 5
    
    'Convert to FMECA format, and set matrix column
    End If
        If likelihood = "5 - Catastrophic" Or likelihood = "5" Or 
LCase(likelihood) = "ca" Or LCase(likelihood) = "5" Or LCase(likelihood) = 
"catastrophic" Then
        Range("j" & iRow).Value = "5 - Catastrophic"
        ilikelihood = 5
    ElseIf likelihood = "4 - Major" Or likelihood = "4" Or LCase(likelihood) 
= "ma" Or LCase(likelihood) = "4" Or LCase(likelihood) = "major" Then
        Range("j" & iRow).Value = "4 - Major"
        ilikelihood = 4
    ElseIf likelihood = "3 - Moderate" Or likelihood = "3" Or 
LCase(likelihood) = "mo" Or LCase(likelihood) = "3" Or LCase(likelihood) = 
"moderate" Then
        Range("j" & iRow).Value = "3 - Moderate"
        ilikelihood = 3
    ElseIf likelihood = "2 - Minor" Or likelihood = "2" Or LCase(likelihood) 
= "mi" Or LCase(likelihood) = "2" Or LCase(likelihood) = "minor" Then
        Range("j" & iRow).Value = "2 - Moderate"
        ilikelihood = 2
    ElseIf likelihood = "1 - Insignificant" Or likelihood = "1" Or 
LCase(likelihood) = "in" Or LCase(likelihood) = "1" Or LCase(likelihood) = 
"insignificant" Then
        Range("j" & iRow).Value = "1 - Insignificant"
        ilikelihood = 1
    Else
        ilikelihood = "0"
    -- 
Thanks as always

Lise 
0
Utf
1/28/2010 3:55:01 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
646 Views

Similar Articles

[PageSpeed] 12

Lise

I have rewritten the code and used SELECT/CASE statements which will reduce 
the code..The line Range("K" & irow).Interior.ColorIndex = 3 + iconsequence 
will change the color of cell K based on the value of the variable 
iconsequence. Hope this helps

'/Convert to FMECA format, and set matrix row
Select Case UCase(Left(consequence, 1))
Case "A"
    Range("i" & irow).Value = "A - Almost Certain"
    iconsequence = 1
Case "B", "L"
    Range("i" & irow).Value = "B - Likely"
    iconsequence = 2
Case "C", "P"
    Range("i" & irow).Value = "C - Possible"
    iconsequence = 3
Case "D", "U"
    Range("i" & irow).Value = "D - Unlikely"
    iconsequence = 4
Case "E", "R"
    Range("i" & irow).Value = "E - Rare"
    iconsequence = 5
'/Convert to FMECA format, and set matrix column
End Select
Range("K" & irow).Interior.ColorIndex = 3 + iconsequence
        
Select Case UCase(Trim(Left(likelihood, 2)))
Case "5", "CA"
        Range("j" & irow).Value = "5 - Catastrophic"
        ilikelihood = 5
Case "4", "MA"
        Range("j" & irow).Value = "4 - Major"
        ilikelihood = 4
Case "3", "MO"
        Range("j" & irow).Value = "3 - Moderate"
        ilikelihood = 3
Case "2", "MI"
        Range("j" & irow).Value = "2 - Moderate"
        ilikelihood = 2
Case "1", "IN"
        Range("j" & irow).Value = "1 - Insignificant"
        ilikelihood = 1
Case Else
        ilikelihood = 0
End Select

-- 
Jacob


"Lise" wrote:

> Hi
> 
> New to VBA so apologies in advance if a dumb question!
> 
> I have managed to get to the following which is working well but I now want 
> corresponding cells in the "k" column to change to specific colours based on 
> the data in "I" and "J" columns and seem to have put myself in a corner - 
> what os the best way to write such a request please?
>    
>     'Convert to FMECA format, and set matrix row
>     If consequence = "A - Almost Certain" Or LCase(consequence) = "a" Or 
> LCase(consequence) = "a" Or LCase(consequence) = "almost certain" Then
>         Range("i" & iRow).Value = "A - Almost Certain"
>         iconsequence = 1
>     ElseIf consequence = "B - Likely" Or LCase(consequence) = "b" Or 
> LCase(consequence) = "l" Or LCase(consequence) = "likely" Then
>         Range("i" & iRow).Value = "B - Likely"
>         iconsequence = 2
>     ElseIf consequence = "C - Possible" Or LCase(consequence) = "c" Or 
> LCase(consequence) = "p" Or LCase(consequence) = "possible" Then
>         Range("i" & iRow).Value = "C - Possible"
>         iconsequence = 3
>     ElseIf consequence = "D - Unlikely" Or LCase(consequence) = "d" Or 
> LCase(consequence) = "u" Or LCase(consequence) = "unlikely" Then
>         Range("i" & iRow).Value = "D - Unlikely"
>         iconsequence = 4
>     ElseIf consequence = "E - Rare" Or LCase(consequence) = "e" Or 
> LCase(consequence) = "r" Or LCase(consequence) = "rare" Then
>         Range("i" & iRow).Value = "E - Rare"
>         iconsequence = 5
>     
>     'Convert to FMECA format, and set matrix column
>     End If
>         If likelihood = "5 - Catastrophic" Or likelihood = "5" Or 
> LCase(likelihood) = "ca" Or LCase(likelihood) = "5" Or LCase(likelihood) = 
> "catastrophic" Then
>         Range("j" & iRow).Value = "5 - Catastrophic"
>         ilikelihood = 5
>     ElseIf likelihood = "4 - Major" Or likelihood = "4" Or LCase(likelihood) 
> = "ma" Or LCase(likelihood) = "4" Or LCase(likelihood) = "major" Then
>         Range("j" & iRow).Value = "4 - Major"
>         ilikelihood = 4
>     ElseIf likelihood = "3 - Moderate" Or likelihood = "3" Or 
> LCase(likelihood) = "mo" Or LCase(likelihood) = "3" Or LCase(likelihood) = 
> "moderate" Then
>         Range("j" & iRow).Value = "3 - Moderate"
>         ilikelihood = 3
>     ElseIf likelihood = "2 - Minor" Or likelihood = "2" Or LCase(likelihood) 
> = "mi" Or LCase(likelihood) = "2" Or LCase(likelihood) = "minor" Then
>         Range("j" & iRow).Value = "2 - Moderate"
>         ilikelihood = 2
>     ElseIf likelihood = "1 - Insignificant" Or likelihood = "1" Or 
> LCase(likelihood) = "in" Or LCase(likelihood) = "1" Or LCase(likelihood) = 
> "insignificant" Then
>         Range("j" & iRow).Value = "1 - Insignificant"
>         ilikelihood = 1
>     Else
>         ilikelihood = "0"
>     -- 
> Thanks as always
> 
> Lise 
0
Utf
1/28/2010 4:49:01 AM
Hi Jacob sorry its taken me a while to come back I have been trying to fiddle 
- but to no avail - perhaps i should have given you the whole page. I just 
cant get the colours to do what I want - tried entering your part but still 
nothing.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim old As Integer
Dim matrix(1 To 6, 1 To 4) As String
Dim cellcolour(1 To 6, 1 To 4) As Integer

iMatrixRow = 1
iMatrixColumn = 1
For iColumn = 3 To 6
    For iRow = 5 To 10
        matrix(iMatrixRow, iMatrixColumn) = Sheets("Tables").Cells(iRow, 
iColumn).Value
        cellcolour(iMatrixRow, iMatrixColumn) = Sheets("Tables").Cells(iRow, 
iColumn).Interior.ColorIndex
        iMatrixRow = iMatrixRow + 1
    Next iRow
    iMatrixRow = 1
    iMatrixColumn = iMatrixColumn + 1
Next iColumn
'Change E to H
'Change C to I
'Change F to J
For iRow = 6 To 100
    consequence = Range("I" & iRow).Value
    likelihood = Range("J" & iRow).Value
    'determine old cell contents to see if it changes. If not, do not update 
colour else it will affect copy and paste function on the worksheet.
    oldrisk = Range("K" & iRow).Value
    
    'Convert to FMECA format, and set matrix row
    If consequence = "A - Almost Certain" Or LCase(consequence) = "a" Or 
LCase(consequence) = "a" Or LCase(consequence) = "almost certain" Then
        Range("i" & iRow).Value = "A - Almost Certain"
        iconsequence = 1
    ElseIf consequence = "B - Likely" Or LCase(consequence) = "b" Or 
LCase(consequence) = "l" Or LCase(consequence) = "likely" Then
        Range("i" & iRow).Value = "B - Likely"
        iconsequence = 2
    ElseIf consequence = "C - Possible" Or LCase(consequence) = "c" Or 
LCase(consequence) = "p" Or LCase(consequence) = "possible" Then
        Range("i" & iRow).Value = "C - Possible"
        iconsequence = 3
    ElseIf consequence = "D - Unlikely" Or LCase(consequence) = "d" Or 
LCase(consequence) = "u" Or LCase(consequence) = "unlikely" Then
        Range("i" & iRow).Value = "D - Unlikely"
        iconsequence = 4
    ElseIf consequence = "E - Rare" Or LCase(consequence) = "e" Or 
LCase(consequence) = "r" Or LCase(consequence) = "rare" Then
        Range("i" & iRow).Value = "E - Rare"
        iconsequence = 5
    
    'Convert to FMECA format, and set matrix column
    End If
        If likelihood = "5 - Catastrophic" Or likelihood = "5" Or 
LCase(likelihood) = "ca" Or LCase(likelihood) = "5" Or LCase(likelihood) = 
"catastrophic" Then
        Range("j" & iRow).Value = "5 - Catastrophic"
        ilikelihood = 5
    ElseIf likelihood = "4 - Major" Or likelihood = "4" Or LCase(likelihood) 
= "ma" Or LCase(likelihood) = "4" Or LCase(likelihood) = "major" Then
        Range("j" & iRow).Value = "4 - Major"
        ilikelihood = 4
    ElseIf likelihood = "3 - Moderate" Or likelihood = "3" Or 
LCase(likelihood) = "mo" Or LCase(likelihood) = "3" Or LCase(likelihood) = 
"moderate" Then
        Range("j" & iRow).Value = "3 - Moderate"
        ilikelihood = 3
    ElseIf likelihood = "2 - Minor" Or likelihood = "2" Or LCase(likelihood) 
= "mi" Or LCase(likelihood) = "2" Or LCase(likelihood) = "minor" Then
        Range("j" & iRow).Value = "2 - Moderate"
        ilikelihood = 2
    ElseIf likelihood = "1 - Insignificant" Or likelihood = "1" Or 
LCase(likelihood) = "in" Or LCase(likelihood) = "1" Or LCase(likelihood) = 
"insignificant" Then
        Range("j" & iRow).Value = "1 - Insignificant"
        ilikelihood = 1
    Else
        ilikelihood = "0"
    End If
    'set matrix number in HRI cell
    If ilikelihood >= 1 And ilikelihood <= 6 And iconsequence >= 1 And 
iconsequence <= 4 Then
        Range("K" & iRow).Value = matrix(ilikelihood, iconsequence)
        'update new cell colours, only if HRI number has changed
        If Range("K" & iRow).Value <> oldrisk Then
            Range("K" & iRow).Interior.ColorIndex = cellcolour(ilikelihood, 
iconsequence)
        End If
        'update cell colour if matrix colour changes
        If Range("K" & iRow).Interior.ColorIndex <> cellcolour(ilikelihood, 
iconsequence) Then
            Range("K" & iRow).Interior.ColorIndex = cellcolour(ilikelihood, 
iconsequence)
        End If
    Else 'delete matrix number in HRI cell
        Range("K" & iRow).Value = ""
        'clear old HRI cell colour if no longer valid
        Range("K" & iRow).Interior.ColorIndex = 0
    End If

Next iRow


iMatrixRow = 1
iMatrixColumn = 1
For iColumn = 3 To 6
    For iRow = 5 To 10
        matrix(iMatrixRow, iMatrixColumn) = Sheets("Tables").Cells(iRow, 
iColumn).Value
        cellcolour(iMatrixRow, iMatrixColumn) = Sheets("Tables").Cells(iRow, 
iColumn).Interior.ColorIndex
        iMatrixRow = iMatrixRow + 1
    Next iRow
    iMatrixRow = 1
    iMatrixColumn = iMatrixColumn + 1
Next iColumn
'Change H to L
'Change I to M
'Change J to N
For iRow = 6 To 100
    consequence = Range("M" & iRow).Value
    likelihood = Range("N" & iRow).Value
    'determine old cell contents to see if it changes. If not, do not update 
colour else it will affect copy and paste function on the worksheet.
    oldrisk = Range("O" & iRow).Value
    
    'Convert to FMECA format, and set matrix row
      If consequence = "A - Almost Certain" Or LCase(consequence) = "a" Or 
LCase(consequence) = "ac" Or LCase(consequence) = "almost certain" Then
        Range("M" & iRow).Value = "A - Almost Certain"
        iconsequence = 1
    ElseIf consequence = "B - Likely" Or LCase(consequence) = "b" Or 
LCase(consequence) = "l" Or LCase(consequence) = "likely" Then
        Range("M" & iRow).Value = "B - Likely"
        iconsequence = 2
    ElseIf consequence = "C - Possible" Or LCase(consequence) = "c" Or 
LCase(consequence) = "p" Or LCase(consequence) = "possible" Then
        Range("M" & iRow).Value = "C - Possible"
        iconsequence = 3
    ElseIf consequence = "D - Unlikely" Or LCase(consequence) = "d" Or 
LCase(consequence) = "u" Or LCase(consequence) = "unlikely" Then
        Range("M" & iRow).Value = "D - Unlikely"
        iconsequence = 4
    ElseIf consequence = "E - Rare" Or LCase(consequence) = "e" Or 
LCase(consequence) = "r" Or LCase(consequence) = "rare" Then
        Range("M" & iRow).Value = "E - Rare"
        iconsequence = 5
    
    Else
        iconsequence = "0"
    End If
    'Convert to FMECA format, and set matrix column
    If likelihood = "5 - Catastrophic" Or likelihood = "5" Or 
LCase(likelihood) = "ca" Or LCase(likelihood) = "5" Or LCase(likelihood) = 
"catastrophic" Then
        Range("N" & iRow).Value = "5 - Catastrophic"
        ilikelihood = 5
    ElseIf likelihood = "4 - Major" Or likelihood = "4" Or LCase(likelihood) 
= "ma" Or LCase(likelihood) = "4" Or LCase(likelihood) = "major" Then
        Range("N" & iRow).Value = "4 - Major"
        ilikelihood = 4
    ElseIf likelihood = "3 - Moderate" Or likelihood = "3" Or 
LCase(likelihood) = "mo" Or LCase(likelihood) = "3" Or LCase(likelihood) = 
"moderate" Then
        Range("N" & iRow).Value = "3 - Moderate"
        ilikelihood = 3
    ElseIf likelihood = "2 - Minor" Or likelihood = "2" Or LCase(likelihood) 
= "mi" Or LCase(likelihood) = "2" Or LCase(likelihood) = "minor" Then
        Range("N" & iRow).Value = "2 - Moderate"
        ilikelihood = 2
    ElseIf likelihood = "1 - Insignificant" Or likelihood = "1" Or 
LCase(likelihood) = "in" Or LCase(likelihood) = "1" Or LCase(likelihood) = 
"insignificant" Then
        Range("N" & iRow).Value = "1 - Insignificant"
        ilikelihood = 1
    Else
        ilikelihood = "0"
    End If
    'set matrix number in HRI cell
    If ilikelihood >= 1 And ilikelihood <= 6 And iconsequence >= 1 And 
iconsequence <= 4 Then
        Range("O" & iRow).Value = matrix(ilikelihood, iconsequence)
        'update new cell colours, only if HRI number has changed
        If Range("O" & iRow).Value <> oldrisk Then
            Range("O" & iRow).Interior.ColorIndex = cellcolour(ilikelihood, 
iconsequence)
        End If
        'update cell colour if matrix colour changes
        If Range("O" & iRow).Interior.ColorIndex <> cellcolour(ilikelihood, 
iconsequence) Then
            Range("O" & iRow).Interior.ColorIndex = cellcolour(ilikelihood, 
iconsequence)
        End If
    Else 'delete matrix number in HRI cell
        Range("O" & iRow).Value = ""
        'clear old HRI cell colour if no longer valid
        Range("O" & iRow).Interior.ColorIndex = 0
    End If

Next iRow

End Sub
-- 
Thanks as always

Kiwi


"Jacob Skaria" wrote:

> Lise
> 
> I have rewritten the code and used SELECT/CASE statements which will reduce 
> the code..The line Range("K" & irow).Interior.ColorIndex = 3 + iconsequence 
> will change the color of cell K based on the value of the variable 
> iconsequence. Hope this helps
> 
> '/Convert to FMECA format, and set matrix row
> Select Case UCase(Left(consequence, 1))
> Case "A"
>     Range("i" & irow).Value = "A - Almost Certain"
>     iconsequence = 1
> Case "B", "L"
>     Range("i" & irow).Value = "B - Likely"
>     iconsequence = 2
> Case "C", "P"
>     Range("i" & irow).Value = "C - Possible"
>     iconsequence = 3
> Case "D", "U"
>     Range("i" & irow).Value = "D - Unlikely"
>     iconsequence = 4
> Case "E", "R"
>     Range("i" & irow).Value = "E - Rare"
>     iconsequence = 5
> '/Convert to FMECA format, and set matrix column
> End Select
> Range("K" & irow).Interior.ColorIndex = 3 + iconsequence
>         
> Select Case UCase(Trim(Left(likelihood, 2)))
> Case "5", "CA"
>         Range("j" & irow).Value = "5 - Catastrophic"
>         ilikelihood = 5
> Case "4", "MA"
>         Range("j" & irow).Value = "4 - Major"
>         ilikelihood = 4
> Case "3", "MO"
>         Range("j" & irow).Value = "3 - Moderate"
>         ilikelihood = 3
> Case "2", "MI"
>         Range("j" & irow).Value = "2 - Moderate"
>         ilikelihood = 2
> Case "1", "IN"
>         Range("j" & irow).Value = "1 - Insignificant"
>         ilikelihood = 1
> Case Else
>         ilikelihood = 0
> End Select
> 
> -- 
> Jacob
> 
> 
> "Lise" wrote:
> 
> > Hi
> > 
> > New to VBA so apologies in advance if a dumb question!
> > 
> > I have managed to get to the following which is working well but I now want 
> > corresponding cells in the "k" column to change to specific colours based on 
> > the data in "I" and "J" columns and seem to have put myself in a corner - 
> > what os the best way to write such a request please?
> >    
> >     'Convert to FMECA format, and set matrix row
> >     If consequence = "A - Almost Certain" Or LCase(consequence) = "a" Or 
> > LCase(consequence) = "a" Or LCase(consequence) = "almost certain" Then
> >         Range("i" & iRow).Value = "A - Almost Certain"
> >         iconsequence = 1
> >     ElseIf consequence = "B - Likely" Or LCase(consequence) = "b" Or 
> > LCase(consequence) = "l" Or LCase(consequence) = "likely" Then
> >         Range("i" & iRow).Value = "B - Likely"
> >         iconsequence = 2
> >     ElseIf consequence = "C - Possible" Or LCase(consequence) = "c" Or 
> > LCase(consequence) = "p" Or LCase(consequence) = "possible" Then
> >         Range("i" & iRow).Value = "C - Possible"
> >         iconsequence = 3
> >     ElseIf consequence = "D - Unlikely" Or LCase(consequence) = "d" Or 
> > LCase(consequence) = "u" Or LCase(consequence) = "unlikely" Then
> >         Range("i" & iRow).Value = "D - Unlikely"
> >         iconsequence = 4
> >     ElseIf consequence = "E - Rare" Or LCase(consequence) = "e" Or 
> > LCase(consequence) = "r" Or LCase(consequence) = "rare" Then
> >         Range("i" & iRow).Value = "E - Rare"
> >         iconsequence = 5
> >     
> >     'Convert to FMECA format, and set matrix column
> >     End If
> >         If likelihood = "5 - Catastrophic" Or likelihood = "5" Or 
> > LCase(likelihood) = "ca" Or LCase(likelihood) = "5" Or LCase(likelihood) = 
> > "catastrophic" Then
> >         Range("j" & iRow).Value = "5 - Catastrophic"
> >         ilikelihood = 5
> >     ElseIf likelihood = "4 - Major" Or likelihood = "4" Or LCase(likelihood) 
> > = "ma" Or LCase(likelihood) = "4" Or LCase(likelihood) = "major" Then
> >         Range("j" & iRow).Value = "4 - Major"
> >         ilikelihood = 4
> >     ElseIf likelihood = "3 - Moderate" Or likelihood = "3" Or 
> > LCase(likelihood) = "mo" Or LCase(likelihood) = "3" Or LCase(likelihood) = 
> > "moderate" Then
> >         Range("j" & iRow).Value = "3 - Moderate"
> >         ilikelihood = 3
> >     ElseIf likelihood = "2 - Minor" Or likelihood = "2" Or LCase(likelihood) 
> > = "mi" Or LCase(likelihood) = "2" Or LCase(likelihood) = "minor" Then
> >         Range("j" & iRow).Value = "2 - Moderate"
> >         ilikelihood = 2
> >     ElseIf likelihood = "1 - Insignificant" Or likelihood = "1" Or 
> > LCase(likelihood) = "in" Or LCase(likelihood) = "1" Or LCase(likelihood) = 
> > "insignificant" Then
> >         Range("j" & iRow).Value = "1 - Insignificant"
> >         ilikelihood = 1
> >     Else
> >         ilikelihood = "0"
> >     -- 
> > Thanks as always
> > 
> > Lise 
0
Utf
2/4/2010 2:07:01 AM
Reply:

Similar Artilces:

need cell appeat blank if no data in that row
I have a data in columns, I want data to be shown only if there is data in that row if no data then leave it blank (here is what I am asking for) Stock trade Gain/Loss Gain/Loss all trades abc 4.18% 4.18% def 3.77% 7.95% [no trade] N/A 7.95% [no trade] N/A 7.95% i want trade column to appear blank in cell of gain/loss for all trade instead of showing 7.95%. if no trade made If there is no data but the formula is in column, third line shows 7.95%, 4th line shows 7.95%, 5th line 7.95...

using excell for analysis of variance (anova)
...

Using Word 2000 as e-mail editor for Outlook 2002
I upgraded to Outlook 2002 while leaving Word 2000. Is there a way to make word the e-mail editor and to ensure all messages come in as rich text? Word and Outlook versions must match to do that. "Dave" <dmichelman@chesapeakeacademy.com> wrote in message news:016701c34d66$b2bed680$a601280a@phx.gbl... > I upgraded to Outlook 2002 while leaving Word 2000. Is > there a way to make word the e-mail editor and to ensure > all messages come in as rich text? ...

How do I add sound to an email using Microsoft Outlook 2002 XP?
I'm looking to send an email that has a song playing in the background when the recipient opens it. I'm using Outlook 2002 for XP and when I select Format, and Background there is not option for Sound. You can do this when you set Word as the e-mail editor; http://www.howto-outlook.com/howto/addingsound.htm -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Anthony" <Anthony@discussions.microsoft.com> wrote in message news:593CCEA2-D565-42F...

Is it possible to run more than one macro using one command button? if yes, how??
Hello, I had many macros that i want to run them all at once but not upon the opening of the excel workbook. i want to have one command button that runs them all. is it feasible?? Samer Gamal Hi Create another Macro that calls each other macro in turn. Sub RunAll() Call Macro1 Call Macro2 .. .. .. Call MacroX End Sub -- Regards Roger Govier <samergadelrab@gmail.com> wrote in message news:d57632d6-d468-4a71-b8f6-c9a87ce9150e@p25g2000hsf.googlegroups.com... > Hello, > I had many macros that i want to run them all at once but not upon the > opening of the excel workbook. i ...

Getting a macro to see the next available empty cell
One of my sheets has several identical forms with different data in the cells. There is a button on each form linking that sheet to another. When I press that button it goes to the other sheet and autofill 3 cells from the previous sheet and form. What I need to do, as the new sheet has space for multiple entries, is when I click the button from another form it recognises that data already exists in the 3 cells and automatically fills the next 3 available unprotected cells. I have been at this for weeks and I am unable to find a solution in the help file. Much obliged to any assist...

using AND in a countif formula
I am trying to make a formula that gives me a count of how many row have the same date and time...i have tried everything i can think o and can't seem to get it...any help is appreciated! i.e. saturday 14:00 saturday 11:00 saturday 14:00 saturday 14:00 saturday 14:00 monday 14:00 monday 14:00 tuesday 4:00 tuesday 14:00 tuesday 1:00 there will be 7 fields...one for each day and time...so in the abov example they should display:(i left a lot of fields out below...onl mentioned the ones that are used in the example) saturday/14:00 - 4 saturday/11:00...

How To programmatically add and retrieve documents using OLE Objects
Hi all. I have an Access database with linked tables to SQL Server 2005. I have a table with a field defined as a VarBinary which is to be used to store documents (word, excel etc) Does anyone have sample VBA code to (1) insert or update the document to the linked table (2) retrieve the data and write to the file system. I have done this via .NET successfully but need to do it via VBA and am struggling to do this as my VBA experience is rusty to say the least. All help and direction greatly appreciated. Thanks Raj On 16 Jul, 17:00, Londoner101 <rajpate...@hotmail.com> wrote: >...

Adding documentation to VBA functions.
Hi How do I add documentation/desriptions for VBA functions or function variables? I have tried to add a description to a function's properties under its Memeber Options in VBA but the description is not displayed when the function is called in MS Excel. Jacques Jacques, You need Laurent Longre's FUNCUSTOMIZE utility .Get it at http://longre.free.fr/english/ -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Jacques Maartens" <someone@somewhere.com> wrote in message news:c0...

Touchscreen Colours
Does anybody know why, no matter what colour is selected for the text on the Touchscreen keyboard, it always shows as white!!!? ...

excel vba problem
Hi, I have columns that I tried to change the cell information t another cell that have to be the blank for a row that contains directo name in cell A and for the movie titles I have to have director name instead of the titles.I totally lost, please help me, the data in cel B sholuld have to be, A 1 D:B.Jane 2 T:Back to the future 3 T:Aliens 4 T:X-files 5 D:Adam Mathew 6 T:Core 7 T:Titanic B 1 (empty) 2 D:B.Jane 3 D:B.Jane 4 D:B.Jane 5 (empty) 6 D:Adam Mathew 7 D:Adam Mathew Thanks in advance for any suggestions -- Message posted from http://www.ExcelForum.com please try the formula list...

Using VBA to set up a query in acViewPivotChart
Using MS Access 2007 VBA once I have opened a query in PivotChart view with the following: DoCmd.OpenQuery "MyQuery", acViewPivotChart How can I set the following: 1) Filter 2) Data 3) Series 4) Category The user can "Drag & Drop" items from the popup Field List. How can I complete this using VBA? Here is a solution to my situation that I have been able to piece together and get to a rough state using a single form rather than multiples and I do NOT have to use the OWC reference to make it work. It is incomplete and still under construction ...

How do I change the width of an individual cell?
I am trying to adjust the width of an individual cell and all I am able to do is adjust the width of the entire column. Any suggestions? Thank you That is all you can do. Ola "Tabitha" <Tabitha@discussions.microsoft.com> skrev i meddelandet news:E138E608-96BB-4888-B886-36598D36E3CF@microsoft.com... >I am trying to adjust the width of an individual cell and all I am able to >do > is adjust the width of the entire column. Any suggestions? > > Thank you You can select two or more cells and merge them (Format>Cells, Alignment tab) However, this can cau...

using ADODC
Hello all, I am trying to use ADODC control in my application. I want to show the fields of a record in edit boxes and then enable scrolling via the ADODC control to move to another record. I am unable to find any example of adodc usage and implementation in vc++ on the internet. If someone has any sample code, any help is greatly appreciated. Thanks ...

using VBA to print Header & footer
Hi, What VBA code should I use in a worksheet so it will print a header & Footer? On Nov 28, 12:43=A0pm, nickbest_l...@hotmail.com wrote: > Hi, > What VBA code should I use in a worksheet so it will print a header & > Footer? Have you tried doing it manually while recording a macro...?? On Nov 28, 9:29=A0pm, Don Guillett <dguille...@gmail.com> wrote: > On Nov 28, 12:43=A0pm, nickbest_l...@hotmail.com wrote: > > > Hi, > > What VBA code should I use in a worksheet so it will print a header & > > Footer? > > Have you tried doing it manua...

Use Different Delivery times for oversize messages
Hi. I having a problem with setting up the option "Use Different Delivery times for oversize messages" When I set the option in the SMTP connector its all look fine. And when I send a mail that is big in size than I can see in the queues that it is schedule to be sent at night. the problem : when it's time to send the mail the server is not sending them... or sometimes it is sending only one mail and the rest is "stuck" Any ides??? Thanks in advance Another thing that I have been notice is that in the queue's you can see that only one message is schedule...

Cannot refer to values of cells in another worksheet
I have a worksheet named Items. I am trying to refer to the values in another worksheet called Dist (same workbook). The value in the Dist worksheet at F6 = 1906, and the value in Dist worksheet at AO6 = 100. So at Items A1, in the formula box, I type =Concatenate(Dist!F6," ",Dist!AO6) because I want to refer to the numbers as text. But instead of showing me 1906 100 all I see is the formula I typed instead of the formula's value. When I type the same formula in a cell for the Dist worksheet it works fine, but when I change to another worksheet it doesn't work.What am I doi...

Extend Rows and related formula downwards using functions not macros
I have a simple worksheet to amortise a loan. I would like to use a formula to extend the rows downwards to the number of payments (Number of years x Number of payments per year) using a formula. For example, if there were 26 payments per year over 1 year, there should be 26 rows. Now if I change the number of years to 2 years, two things should happen: 1. The number of rows should expand to 52 from 26 2. The sum of the interest paid should include these extra rows.i.e =Sum(rownumber 1 to row number 52) instead of Sum (rownumber 1 to rownumber 26). Currently I am using a simple formula alon...

Anyone using Auto Accept Agent with a customized XML config file?
Is anyone using the Auto Accept Agent and has customized the agent's XML config file to give customized repsones for individual mailboxes or change from the agent default settings? I have followed the Auto Accept Agent administration guide to the letter on this with no luck. the agent always uses its default settings. The XML file is valid and there are no messages in the logs. Restarting the service doesn't help either. This is on an Exchange 2003 Enterprise edition server running on W2K3 Server SP1. I haven't run across anyone who has it working, only a few like me who c...

#5.1.6 Error "The message was undeliverable because the recipient when using 'All at HQ' distribution list
Hi, We have found that whenever an email is sent to the distribution list 'all at HQ', an NDR (below) is returned for one of the users. When an email is sent to her directly it is fine however. I looked this error up, and the suggestion was that the user's email address has changed, but this is not the case. This happened previously, and was resolved very easily by just removing the user object from the distribution group and re adding it. I did this again for all the groups, but the error still occurs when a mass mail is sent out. The full error is below: Your message did not ...

AutoSave File After X Time In VBA #2
Thanks for the help....... But is there a way to create a macro that will save the orriginal fil periodically, instead of relying on the autosave function on Excel? Maybe using the SaveAs and Wait functions? Celtic_Avenger :confused: :confused: :confused: :confused: :confused -- Celtic_Avenge ----------------------------------------------------------------------- Celtic_Avenger's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1410 View this thread: http://www.excelforum.com/showthread.php?threadid=26126 Look at application.ontime Chip Pearson explains i...

Highlite current/active cell
Excel 2000 I use Edit/Find frequently, but have a hard time seeing the 'found' cell. Is there a way to change the look (background color) used for the active cell? I have come across the "RowLiner" add-in and other fancy solution, but I would just like something simple, like changing the active cell color. Thanks here is a link with directions to highlight the active cell Highlight the Active Cell, by Andrew Siffert http://blog.livedoor.jp/andrewe/archives/50061563.html Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that cove...

Merged Cells Prevent sorting
I'm trying to sort upon a given column and I get the error message stating that "Merged cells must be of the same size." I know what merged cells are, but the problem is, I cannot find ANY cells in the column in which I am sorting that are merged. So does this rule apply to ANY CELL in ANY column? If so, how do I bypass or shut off this feature so that I can perform what I thought was going to be a simple sorting of date on a single field? Select all the cells in the entire worksheet and in Format > Cells... clear the merge checkbox -- Gary's Student gsnu200706 ...

Archiving using Outlook 2002
I had never used the archiving ability of Outlook until a few days ago. For years, I had been saving my messages in the "Saved Mail" folder, using a complex tree structure. Eventually, however, this tree became so unwieldy that I asked someone here how to save these emails to Word. They were kind enough to help me, telling me that Outlook would not export to Word, and suggested that I archive them instead. I worked through the archival feature, and was very pleased with the way it saved all the emails I had carefully preserved for the last several years. I even set up a re...

Can I use Outlook 2002 as newsreader?
Thanks! On Tue, 26 Oct 2004 09:08:55 +0200, "Clau" <clau@edil.ro> wrote: >Thanks! > Howdy, Click View | Go To | News... Outlook will then launch Outlook Express to read newsgroups. Outlook itself has no news functionality... HTH, -- Kenneth If you email... Please remove the "SPAMLESS." ...