# Thread: Expanding formula to a range...

1. ## 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

2. 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?

3. 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. 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!

5. Originally Posted by RetiredGeek
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. Hey Zeddy,

Thanks for your input.

Originally Posted by zeddy
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

7. 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. Zeddy,

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

HTH

#### Posting Permissions

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