Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Expanding formula to a range...

    Hi Guys,

    How can I extend this to a range?
    Range is Target.Offset(4 - r) to Target.Offset(8 - r).


    Code:
        Target.Offset(2 - r, -1).Font.Color = RGB(255, 255, 255)                'turn seller name white
        If Target.Offset(4 - r) = 0 And Target.Offset(4 - r) <> "" Then         'turn seller name black if item sold
            MsgBox "Going Black!"
            Target.Offset(2 - r, -1).Font.Color = RGB(0, 0, 0)
        End If

    What is important here:

    - If all Blank > Turn Seller name White
    - If Sum of range > 0 Turn Seller name White
    - Else Keep Seller Name Black

    Thanks
    Ferenc
    Last edited by Ferenc Nagy; 2015-07-21 at 13:15.

  2. #2
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    I figured out this as a solution:

    Code:
        'Works    '-----------------------------------
        Target.Offset(2 - r, -1).Font.Color = RGB(0, 0, 0)                      'Turn Seller name black
        x = 0
        
        If Target.Offset(4 - r) <> "" Then                                      'Check if cells are not blank
        MsgBox "Not Blank 1"
        x = x + 1
        End If
        If Target.Offset(5 - r) <> "" Then
        MsgBox "Not Blank 2"
        x = x + 1
        End If
        If Target.Offset(6 - r) <> "" Then
        MsgBox "Not Blank 3"
        x = x + 1
        End If
        If Target.Offset(7 - r) <> "" Then
        MsgBox "Not Blank 4"
        x = x + 1
        End If
        If Target.Offset(8 - r) <> "" Then
        MsgBox "Not Blank 5"
        x = x + 1
        End If
        
        If x = 0 Or WorksheetFunction.Sum(Range(Target.Offset(4 - r), Target.Offset(8 - r))) > 0 Then
        MsgBox "All Blank or more then Zero"
        Target.Offset(2 - r, -1).Font.Color = RGB(255, 255, 255)
        End If
    Then I improved on it with this:

    Code:
        If Target.Offset(4 - r) = "" And Target.Offset(5 - r) = "" And Target.Offset(6 - r) = "" And Target.Offset(7 - r) = "" And Target.Offset(8 - r) = "" Then    MsgBox "Blank 1 to 5"
        End If
    And finally turned it into this:

    Code:
        If Target.Offset(4 - r) = "" And Target.Offset(5 - r) = "" And Target.Offset(6 - r) = "" And Target.Offset(7 - r) = "" And Target.Offset(8 - r) = "" Or WorksheetFunction.Sum(Range(Target.Offset(4 - r), Target.Offset(8 - r))) > 0 Then
        MsgBox "Blank 1 to 5 or more then Zero!"
        End If
    So the final version looks like this:

    Code:
    If Target.Offset(4 - r) = "" And Target.Offset(5 - r) = "" And Target.Offset(6 - r) = "" And Target.Offset(7 - r) = "" And Target.Offset(8 - r) = "" Or WorksheetFunction.Sum(Range(Target.Offset(4 - r), Target.Offset(8 - r))) > 0 Then
        Target.Offset(2 - r, -1).Font.Color = RGB(255, 255, 255)
        Else
         Target.Offset(2 - r, -1).Font.Color = RGB(0, 0, 0)
        End If
    Anyone has a more efficient way to write the first part of the if statement?
    Last edited by Ferenc Nagy; 2015-07-21 at 13:18.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Ferenc

    ..this might help:
    Code:
    '- If all Blank > Turn Seller name White
    '- If Sum of range > 0 Turn Seller name White
    ' - Else Keep Seller Name Black
    
    r = Target.Row
    
    Set zBlock = Target.Offset(4 - r).Resize(5)
    zSum = Application.Sum(zBlock)
    zBlanks = Application.CountBlank(zBlock)
    
    If zBlanks = 5 Or zSum > 0 Then
    Target.Offset(2 - r, -1).Font.Color = vbWhite
    Else
    Target.Offset(2 - r, -1).Font.Color = vbBlack
    End If
    zeddy
    •Excel Impressionist and Modern Art Surveyor
    .

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey Y'all,

    OK Geek, stay calm, oom...oom!

    Ah all calm now....

    [Opinion Start]
    IMHO it's bad coding practice to rely on defaults. When not specified they can lead to assumptions that may or may not be correct.

    Thus, avoid Target.Offset(8 - r) and instead use Target.Offset(8 - r, 0)
    [/Opinion End]

    Ah, I feel much better now!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Hey Y'all,

    OK Geek, stay calm, oom...oom!

    Ah all calm now....

    [Opinion Start]
    IMHO it's bad coding practice to rely on defaults. When not specified they can lead to assumptions that may or may not be correct.

    Thus, avoid Target.Offset(8 - r) and instead use Target.Offset(8 - r, 0)
    [/Opinion End]

    Ah, I feel much better now!
    Thank you, will keep that in mind

  6. #6
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Hey Zeddy,

    Thanks for your input.

    Quote Originally Posted by zeddy View Post
    Hi Ferenc

    ..this might help:
    Code:
    '- If all Blank > Turn Seller name White
    '- If Sum of range > 0 Turn Seller name White
    ' - Else Keep Seller Name Black
    
    r = Target.Row
    
    Set zBlock = Target.Offset(4 - r).Resize(5)
    zSum = Application.Sum(zBlock)
    zBlanks = Application.CountBlank(zBlock)
    
    If zBlanks = 5 Or zSum > 0 Then
    Target.Offset(2 - r, -1).Font.Color = vbWhite
    Else
    Target.Offset(2 - r, -1).Font.Color = vbBlack
    End If
    zeddy
    •Excel Impressionist and Modern Art Surveyor
    .
    The final version of the code looks like this:
    Code:
        '--------------------------------------------                                                                       'Color of Seller
        If .Cells(I, J).Offset(2, 2) = "" And .Cells(I, J).Offset(3, 2) = "" And .Cells(I, J).Offset(4, 2) = "" And .Cells(I, J).Offset(5, 2) = "" And .Cells(I, J).Offset(6, 2) = "" Or WorksheetFunction.Sum(Range(.Cells(I, J).Offset(2, 2), .Cells(I, J).Offset(6, 2))) > 0 Then
        .Cells(I, J).Offset(0, 1).Font.Color = RGB(255, 255, 255)
        Else
        .Cells(I, J).Offset(0, 1).Font.Color = RGB(0, 0, 0)
        End If
    Using your example, I created this...

    Code:
    Set zBlock = .Cells(I, J).Offset(2, 2).Resize(5)
    zSum = Application.Sum(zBlock)
    zBlanks = Application.CountBlank(zBlock)
    
    
    If zBlanks = 5 Or zSum > 0 Then
    .Cells(I, J).Offset(0, 1).Font.Color = vbWhite
    Else
    .Cells(I, J).Offset(0, 1).Font.Color = vbBlack
    End If

    Ferenc
    Last edited by RetiredGeek; 2015-07-21 at 17:23.

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi RG

    Anyone has a more efficient way to write the first part of the if statement?
    ..Target.Offset(4 - r).Resize(5) etc etc etc

    ..it's efficient! it may be bad, but I liked it!
    ..and I mostly agree with everything you say!

    zeddy
    •Coding Team Rehabilitation Therapist

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    ..Target.Offset(4 - r,0).Resize(5)
    Does exactly the same thing but leaves nothing to the imagination!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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