Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing color of cells
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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.522 / Virus Database: 320 - Release Date: 09/29/2003 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing color of cells
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 wrote in message ... 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? 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 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.522 / Virus Database: 320 - Release Date: 09/29/2003 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing color of cells
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. In article , "Carl Brehm" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing color of cells
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 "J.E. McGimpsey" wrote in message ... 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. In article , "Carl Brehm" wrote: 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.522 / Virus Database: 320 - Release Date: 09/29/2003 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing color of cells
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 "Stuart" wrote in message ... 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 wrote in message ... 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? 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 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.522 / Virus Database: 320 - Release Date: 09/29/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.522 / Virus Database: 320 - Release Date: 09/30/2003 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing color of cells
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 In article , "Carl Brehm" wrote: Yours turns all weeks blue, when every other is supposed to be black. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing the color of highlighted cells | Excel Discussion (Misc queries) | |||
Excel 2003 - Color changing cells | Excel Discussion (Misc queries) | |||
Changing the color of cells by formula | Excel Worksheet Functions | |||
Changing several cells text color | Excel Discussion (Misc queries) | |||
color changing of cells | Excel Discussion (Misc queries) |