Changing color of cells

[Carl Brehm]

Need to change color of every other row, but only that portion of row in
the range.
Here is what I have.


Sub working()
Dim x As Integer
Dim response
response = MsgBox("Do you Work the first weekend?", vbYesNo)
If response = vbYes Then
x = 0
Else
x = 1
End If

With Worksheets("Yearly Planner")
For Each cell In .Range("A253")
With cell
If x = 0 Then
.Font.Color = vbBlue ' working weekend
Else
.Font.Color = black 'off weekend
End If
End With

' does Excel go to next cell in row or in column?


End Sub

Thanks Carl
--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN & Red Lories, Quakers
Mitred Conures, TAG's,DYH, Bourkes,
Cages, Toys, Toy parts Wholesale/Retail
Feed & Supplies

 

[Stuart]

Worksheets("Yearly Planner").Select
Range("A253").Select
If MsgBox("Do you Work the first weekend?", vbYesNo) = vbYes Then
x = vbBlue
y = vbBlack
Else
x = vbBlack
y = vbBlue
End If

For Each rng In Selection
If Application.Round(rng.Row / 2, 0) - Application.Round(rng.Row / 2, 2) = 0
Then
rng.Font.Color = x
Else
rng.Font.Color = y
End If
Next rng

Need to change color of every other row, but only that portion of row in
the range.
Here is what I have.


Sub working()
Dim x As Integer
Dim response
response = MsgBox("Do you Work the first weekend?", vbYesNo)
If response = vbYes Then
x = 0
Else
x = 1
End If

With Worksheets("Yearly Planner")
For Each cell In .Range("A253")
With cell
If x = 0 Then
.Font.Color = vbBlue ' working weekend
Else
.Font.Color = black 'off weekend
End If
End With

' does Excel go to next cell in row or in column?

Next cell in Row, excel will go through table left to right then down, same
as reading!

But if you are trying to change the colour of font in the whole of the
range, you don't need to do it cell by cell!

try
Sub working()
Worksheets("Yearly Planner").Select
If MsgBox("Do you Work the first weekend?", vbYesNo) = vbYes Then
Range("A253").Font.Color = vbBlue ' working weekend
Else
Range("A253").Font.Color = vbBlack 'off weekend
End If
End Sub

 

[J.E. McGimpsey]

I'd suggest a slightly different approach:

Public Sub Working()
Dim response As Long
response = MsgBox("Do you work the first weekend?", vbYesNo)
With Range("A253").FormatConditions
.Delete
.Add Type:=xlExpression, _
Formula1:="=MOD(ROW(C3),2)=" & 1 + (response = vbYes)
.Item(1).Font.Color = vbBlue
End With
End Sub

This allows you to add or subtract rows and maintain the font color.

 

[Carl Brehm]

Yours turns all weeks blue, when every other is supposed to be black.

--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN & Red Lories, Quakers
Mitred Conures, TAG's,DYH, Bourkes,
Cages, Toys, Toy parts Wholesale/Retail
Feed & Supplies

 

[Carl Brehm]

This works but only if you choose yes as a response.

Public Sub Working3()
Dim X, Y As Long
Dim RNG

Worksheets("Yearly Planner").Select
Range("A253").Select

If MsgBox("Do you Work the first weekend?", vbYesNo) = vbYes Then
X = vbBlue
Y = vbBlack
Else
X = vbBlack
Y = vbBlue
End If

For Each RNG In Selection
If Application.Round(RNG.Row / 2, 0) - Application.Round(RNG.Row / 2, 2)
= 0 Then
RNG.Font.Color = X
Else
RNG.Font.Color = Y
End If
Next RNG

End Sub

--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN & Red Lories, Quakers
Mitred Conures, TAG's,DYH, Bourkes,
Cages, Toys, Toy parts Wholesale/Retail
Feed & Supplies

 

[J.E. McGimpsey]

No, it doesn't. Check that you matched what I posted. Why I chose C3
in the CF, though, I don't know - this works just as well:

Public Sub Working()
Dim response As Long
response = MsgBox("Do you work the first weekend?", vbYesNo)
With Range("A253").FormatConditions
.Delete
.Add Type:=xlExpression, _
Formula1:="=MOD(ROW(A2),2)=" & -(response = vbYes)
.Item(1).Font.Color = vbBlue
End With
End Sub