Changing color of cells

  • Thread starter Thread starter Carl Brehm
  • Start date Start date
C

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("A2:D53")
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
 
Worksheets("Yearly Planner").Select
Range("A2:D53").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



Carl Brehm said:
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("A2:D53")
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?
Click to expand...

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("A2:D53").Font.Color = vbBlue ' working weekend
Else
Range("A2:D53").Font.Color = vbBlack 'off weekend
End If
End Sub
 
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("A2:D53").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.
 
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
 
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("A2:D53").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
 
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("A2:D53").FormatConditions
.Delete
.Add Type:=xlExpression, _
Formula1:="=MOD(ROW(A2),2)=" & -(response = vbYes)
.Item(1).Font.Color = vbBlue
End With
End Sub
 
Back
Top