+ Reply to Thread
Results 1 to 5 of 5

Change formats based on A:A cell value on each row - (not with conditional format!) ?

  1. #1
    Marie J-son
    Guest

    Change formats based on A:A cell value on each row - (not with conditional format!) ?

    Hi,

    I need VBA code for this, I can't use the conditional format function of
    some reasons.

    Let ? = a row between 6 and 96 and cells in same row between column C:G
    should have different fonts, colorindex etc depending on what letter there
    is in the cell in A column.

    If A? = "K" then cells Range("C?:G?").ColorIndex = 1
    If A? = "I" then cells Range("C?:G?").ColorIndex = 2
    If A? = "R" then cells Range("C?:G?").ColorIndex = 3

    Any suggestions?

    By the way, the fastest way possible please, the VBA code will be into
    worksheet_change. Maybe use "Find" rather than a Loop, maybe?


    /Regards



  2. #2
    Tom Ogilvy
    Guest

    Re: Change formats based on A:A cell value on each row - (not with conditional format!) ?

    for i = 6 to 96

    If Range("A6")(i).value = "K" then cells
    Range("C5:G5").Offset(i,0).ColorIndex = 1
    If Range("A6")(i).value = "I" then cells
    Range("C5:G5").Offset(i,0).ColorIndex = 2
    If Range("A6")(i).value = "R" then cells
    Range("C5:G5").Offset(i,0).ColorIndex = 3

    Next

    --
    Regards,
    Tom Ogilvy

    "Marie J-son" <No@email.com> wrote in message
    news:%23qwyjINRFHA.3672@TK2MSFTNGP10.phx.gbl...
    > Hi,
    >
    > I need VBA code for this, I can't use the conditional format function of
    > some reasons.
    >
    > Let ? = a row between 6 and 96 and cells in same row between column C:G
    > should have different fonts, colorindex etc depending on what letter there
    > is in the cell in A column.
    >
    > If A? = "K" then cells Range("C?:G?").ColorIndex = 1
    > If A? = "I" then cells Range("C?:G?").ColorIndex = 2
    > If A? = "R" then cells Range("C?:G?").ColorIndex = 3
    >
    > Any suggestions?
    >
    > By the way, the fastest way possible please, the VBA code will be into
    > worksheet_change. Maybe use "Find" rather than a Loop, maybe?
    >
    >
    > /Regards
    >
    >




  3. #3
    Bob Phillips
    Guest

    Re: Change formats based on A:A cell value on each row - (not with conditional format!) ?

    Why can't you use CF?

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Marie J-son" <No@email.com> wrote in message
    news:%23qwyjINRFHA.3672@TK2MSFTNGP10.phx.gbl...
    > Hi,
    >
    > I need VBA code for this, I can't use the conditional format function of
    > some reasons.
    >
    > Let ? = a row between 6 and 96 and cells in same row between column C:G
    > should have different fonts, colorindex etc depending on what letter there
    > is in the cell in A column.
    >
    > If A? = "K" then cells Range("C?:G?").ColorIndex = 1
    > If A? = "I" then cells Range("C?:G?").ColorIndex = 2
    > If A? = "R" then cells Range("C?:G?").ColorIndex = 3
    >
    > Any suggestions?
    >
    > By the way, the fastest way possible please, the VBA code will be into
    > worksheet_change. Maybe use "Find" rather than a Loop, maybe?
    >
    >
    > /Regards
    >
    >




  4. #4
    Marie J-son
    Guest

    Re: Change formats based on A:A cell value on each row - (not with conditional format!) ?

    Because I actually already use CF as one of the formats to be
    restored...This VBA code is used with worksheet_change to restore formats in
    case of drag and drop etc., where also CF will be overwritten.

    Actually, Worksheet_Change is not compleatly sufficient either. There are
    events that doesn't trig with WS_change and I'm not fully in control if
    there had to be more codes needed to cover other events.


    Open question: Are there any other events to be covered? We are talking
    aboute ensure that the cell formats will continue to "look right":

    I delete comments, verification.delete and all boxes, fonts and boardes you
    can record when you change Format-Cells; Format Cells window and change or
    delete Conditional Formats.

    /Regards



    "Bob Phillips" <bob.phillips@notheretiscali.co.uk> skrev i meddelandet
    news:e9xmMRNRFHA.3544@TK2MSFTNGP12.phx.gbl...
    > Why can't you use CF?
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Marie J-son" <No@email.com> wrote in message
    > news:%23qwyjINRFHA.3672@TK2MSFTNGP10.phx.gbl...
    >> Hi,
    >>
    >> I need VBA code for this, I can't use the conditional format function of
    >> some reasons.
    >>
    >> Let ? = a row between 6 and 96 and cells in same row between column C:G
    >> should have different fonts, colorindex etc depending on what letter
    >> there
    >> is in the cell in A column.
    >>
    >> If A? = "K" then cells Range("C?:G?").ColorIndex = 1
    >> If A? = "I" then cells Range("C?:G?").ColorIndex = 2
    >> If A? = "R" then cells Range("C?:G?").ColorIndex = 3
    >>
    >> Any suggestions?
    >>
    >> By the way, the fastest way possible please, the VBA code will be into
    >> worksheet_change. Maybe use "Find" rather than a Loop, maybe?
    >>
    >>
    >> /Regards
    >>
    >>

    >
    >




  5. #5
    Peter T
    Guest

    Re: Change formats based on A:A cell value on each row - (not with conditional format!) ?

    > Because I actually already use CF as one of the formats to be
    > restored...This VBA code is used with worksheet_change to restore formats

    in
    > case of drag and drop etc., where also CF will be overwritten.


    So why not replace your original CF's

    Sub Test()
    On Error GoTo errH
    Application.EnableEvents = False
    With ActiveSheet.Range("c6")
    .Activate '
    With .FormatConditions
    .Delete
    .Add xlExpression, , "=$A6=""K"""
    .Item(1).Interior.ColorIndex = 3 'red
    .Add xlExpression, , "=$A6=""I"""
    .Item(2).Interior.ColorIndex = 4 'green
    .Add xlExpression, , "=$A6=""R"""
    .Item(3).Interior.ColorIndex = 5 'blue
    End With
    .Copy
    .Resize(91, 5).PasteSpecial xlPasteFormats ' c6:g96
    End With
    errH:
    Application.EnableEvents = True
    End Sub

    If your old CF's might have been dragged elsewhere, maybe you want to delete
    all CF's in the sheet at the head of the code.

    ActiveSheet.UsedRange.FormatConditions.Delete

    Running the above should be faster than looping and changing formats.
    However if your values K, I & R in col-A don't change regularly, I would
    prefer to format with normal interior colour formats. A bit longer but if
    you're running from a macro from time to time perhaps not an issue.

    > Open question: Are there any other events to be covered? We are talking
    > aboute ensure that the cell formats will continue to "look right":


    Not sure you mean by to be covered, in terms of what?
    But you are correct that not all cell changes trigger an event.

    Regards,
    Peter T

    "Marie J-son" <No@email.com> wrote in message
    news:#3FUziORFHA.3288@TK2MSFTNGP14.phx.gbl...
    > Because I actually already use CF as one of the formats to be
    > restored...This VBA code is used with worksheet_change to restore formats

    in
    > case of drag and drop etc., where also CF will be overwritten.
    >
    > Actually, Worksheet_Change is not compleatly sufficient either. There are
    > events that doesn't trig with WS_change and I'm not fully in control if
    > there had to be more codes needed to cover other events.
    >
    >
    > Open question: Are there any other events to be covered? We are talking
    > aboute ensure that the cell formats will continue to "look right":
    >
    > I delete comments, verification.delete and all boxes, fonts and boardes

    you
    > can record when you change Format-Cells; Format Cells window and change or
    > delete Conditional Formats.
    >
    > /Regards
    >
    >
    >
    > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> skrev i meddelandet
    > news:e9xmMRNRFHA.3544@TK2MSFTNGP12.phx.gbl...
    > > Why can't you use CF?
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Marie J-son" <No@email.com> wrote in message
    > > news:%23qwyjINRFHA.3672@TK2MSFTNGP10.phx.gbl...
    > >> Hi,
    > >>
    > >> I need VBA code for this, I can't use the conditional format function

    of
    > >> some reasons.
    > >>
    > >> Let ? = a row between 6 and 96 and cells in same row between column C:G
    > >> should have different fonts, colorindex etc depending on what letter
    > >> there
    > >> is in the cell in A column.
    > >>
    > >> If A? = "K" then cells Range("C?:G?").ColorIndex = 1
    > >> If A? = "I" then cells Range("C?:G?").ColorIndex = 2
    > >> If A? = "R" then cells Range("C?:G?").ColorIndex = 3
    > >>
    > >> Any suggestions?
    > >>
    > >> By the way, the fastest way possible please, the VBA code will be into
    > >> worksheet_change. Maybe use "Find" rather than a Loop, maybe?
    > >>
    > >>
    > >> /Regards
    > >>
    > >>

    > >
    > >

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1