Automatic alternate row colour

Color code.

Sub FlashBack()
'Make cell range Background color, flash x times, x fast, in x color,
'when Ctrl-a is pressed.

Dim newColor As Integer
Dim myCell As Range
Dim x As Integer
Dim fSpeed

'Make this cell range background flash!
Set myCell = Range("A1:A2")
Application.DisplayStatusBar = True
Application.StatusBar = "... Select Cell to Stop and Edit or Wait for Flashing to Stop! "

'Make cell background flash to this color!
'Black 25, Magenta 26, Yellow 27, Cyan 28, Violet 29, Dark Red 30,
'Teal 31, Blue 32, White 2, Red 3, Light Blue 41, Dark Blue 11,
'Gray-50% 16, Gray-25% 15, Bright Cyan 8.
newColor = 27

'Make the cell range flash fast: 0.01 to slow: 0.99
fSpeed = 0.2

'Make cell flash, this many times!
Do Until x = 35

'Run loop!
DoEvents
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = newColor
Loop
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = xlNone
Loop
x = x + 1
Loop
Application.StatusBar = False
Application.DisplayStatusBar = Application.DisplayStatusBar
End Sub

Sub FlashFont()
'Make cell range font flash, x times, x fast, in x color,
'when Ctrl-z is pressed.
Dim newColor As Integer
Dim myCell As Range
Dim x As Integer
Dim fSpeed

'Make this cell range font flash!
Set myCell = Range("A1:A2")
Application.DisplayStatusBar = True
Application.StatusBar = "... Select Cell to Stop and Edit or Wait for Flashing to Stop! "

'Make cell font flash to this color!
'Black 25, Magenta 26, Yellow 27, Cyan 28, Violet 29, Dark Red 30,
'Teal 31, Blue 32, White 2, Red 3, Light Blue 41, Dark Blue 11,
'Gray-50% 16, Gray-25% 15, Bright Cyan 8.

newColor = 3

'Make the cell range flash fast: 0.01 to slow: 0.99
fSpeed = 0.3

'Make cell flash, this many times!
Do Until x = 20

'Run loop!
DoEvents
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Font.ColorIndex = newColor
Loop
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Font.ColorIndex = xlAutomatic
Loop
x = x + 1
Loop
Application.StatusBar = False
Application.DisplayStatusBar = Application.DisplayStatusBar
End Sub
Sub reSetFlash()
'Re-set cell range color if edit break on color, Ctrl-r to re-set!
ActiveCell.Select
Selection.Interior.ColorIndex = xlNone
End Sub

Macro1 Macro Macro by Joseph S. Was Ctrl-s

Re: Color code. _ Let me rephrase the question

Thanx Joe, but I think I may not have explained it very well. I want to get a list to look as follows

Row1 - yellow
Row2 - red
Row3 - yellow
Row4 - red
etc.

Row5 would be added by a macro - how do i make it yellow?
Row6 red, etc. Sub FlashBack() Make cell range Background color, flash x times, x fast, in x color, when Ctrl-a is pressed. Dim newColor As Integer

Do not change the name of this macro or it will fail! Macro1 Macro Macro by Joseph S. Was Ctrl-s

: I would like to alternate rows between 2 colours in a list. The rows are added to a list by a macro therefore I guess I need to reference to the row number but can't figure it out. Can anyone help please. : Thanx : Sid

VB Macro to Insert a Yellow and Red Row at Cursor.

The code below will insert two rows at the current cursor location, one Yellow and one Red. I tested it by assigning a macro Hot-ket to the code; Ctrl-a. You can modify the code to be, two macros, one to insert a Yellow Row the other to insert a Red Row. Then assign each a new Hot-key, like: Ctrl-y for insert a yellow row at the cursor and Ctrl-r for the red one. JSW

Sub YellowRedRow()
'Code by Jow Was

'At cursor selected Row, Add new Row.
ActiveCell.Select
Selection.EntireRow.Insert

'Color Row Red.
With Selection.EntireRow.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

'Add 2nd Row.
Selection.EntireRow.Insert

'Color Row Yellow.
With Selection.EntireRow.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub

I gave you all the information to do this yourself. I don't understand why you failed to do it!

If you do not know how to copy and paste the above code into a macro module and assign a hot-key to it let me know. JSW Thanx Joe, but I think I may not have explained it very well. I want to get a list to look as follows Row1 - yellow Row2 - red Row3 - yellow Row4 - red etc. Row5 would be added by a macro - how do i make it yellow? Row6 red, etc. : Sub FlashBack() Make cell range Background color, flash x times, x fast, in x color, when Ctrl-a is pressed. : Dim newColor As Integer : Dim myCell As Range : Dim x As Integer : Dim fSpeed : 'Make this cell range background flash! : Set myCell = Range("A1:A2") : Application.DisplayStatusBar = True : Application.StatusBar = "... Select Cell to Stop and Edit or Wait for Flashing to Stop! " : 'Make cell background flash to this color! Black 25, Magenta 26, Yellow 27, Cyan 28, Violet 29, Dark Red 30, Teal 31, Blue 32, White 2, Red 3, Light Blue 41, Dark Blue 11, Gray-50% 16, Gray-25% 15, Bright Cyan 8. : newColor = 27 : 'Make the cell range flash fast: 0.01 to slow: 0.99 : fSpeed = 0.2 : 'Make cell flash, this many times! : Do Until x = 35 : 'Run loop! : DoEvents : Start = Timer : Delay = Start + fSpeed : Do Until Timer > Delay : DoEvents : myCell.Interior.ColorIndex = newColor : Loop : Start = Timer : Delay = Start + fSpeed : Do Until Timer > Delay : DoEvents : myCell.Interior.ColorIndex = xlNone : Loop : x = x + 1 : Loop : Application.StatusBar = False : Application.DisplayStatusBar = Application.DisplayStatusBar : End Sub : Sub FlashFont() Make cell range font flash, x times, x fast, in x color, when Ctrl-z is pressed. : Dim newColor As Integer : Dim myCell As Range : Dim x As Integer : Dim fSpeed : 'Make this cell range font flash! : Set myCell = Range("A1:A2") : Application.DisplayStatusBar = True : Application.StatusBar = "... Select Cell to Stop and Edit or Wait for Flashing to Stop! " : 'Make cell font flash to this color! Black 25, Magenta 26, Yellow 27, Cyan 28, Violet 29, Dark Red 30, Teal 31, Blue 32, White 2, Red 3, Light Blue 41, Dark Blue 11, Gray-50% 16, Gray-25% 15, Bright Cyan 8. : newColor = 3 : 'Make the cell range flash fast: 0.01 to slow: 0.99 : fSpeed = 0.3 : 'Make cell flash, this many times! : Do Until x = 20 : 'Run loop! : DoEvents : Start = Timer : Delay = Start + fSpeed : Do Until Timer > Delay : DoEvents : myCell.Font.ColorIndex = newColor : Loop : Start = Timer : Delay = Start + fSpeed : Do Until Timer > Delay : DoEvents : myCell.Font.ColorIndex = xlAutomatic : Loop : x = x + 1 : Loop : Application.StatusBar = False : Application.DisplayStatusBar = Application.DisplayStatusBar : End Sub : Sub reSetFlash() Re-set cell range color if edit break on color, Ctrl-r to re-set! : ActiveCell.Select : Selection.Interior.ColorIndex = xlNone : End Sub

If anyone is interested, I built a free addin called "Ledger Shade" that does this sort of thing.

One of the features it has is that it can create ledger shading by automatically writing the necessary conditional format logic. You could use the logic in your macro or just use the addin it's up to you.

Regards,
AaronThe Excel Logic Page

Code to color an existing entire row at the current cursor location.

Sub YellowRow()
'Code by Jow Was

'At cursor, select Row, color Yellow.
ActiveCell.Select

'Color Row Yellow.
With Selection.EntireRow.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub

Re: Code to color an existing entire row at the current cursor location.

Joe thanx again - i understand how to colour the row, but I want to automatically alternate - red, yellow, red, etc. I am using the code below to generate a list from several worksheets, the generated list can contain any mix of these sheets dependant on constantly changing values therein.
Therefore the code you gave me must be referenced against the absolute row value - how?

Public Sub BungsFitted()
Application.ScreenUpdating = False
Sheets("Report").Select
FinalRow = Range("A65536").End(xlUp).Row
Range("A2", "L" & FinalRow).Select
Selection.ClearContents

Sheets("AA").Select
' Find the last row of data
FinalRow = Range("A65536").End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
' Decide if to copy based on column K
ThisValue = Range("K" & x).Value
If ThisValue = "" Then
Range("A" & x & ":M" & x).Copy
Sheets("Report").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Select
Sheets("AA").Select

End If
Next x

Sheets("BB").Select
' Find the last row of data
FinalRow = Range("A65536").End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
' Decide if to copy based on column K
ThisValue = Range("K" & x).Value
If ThisValue = "" Then
Range("A" & x & ":M" & x).Copy
Sheets("Report").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("BB").Select

End If
Next x

Application.CutCopyMode = False
Sheets("Report").Select
Range("A2").Select
End Sub

Cheers
Sid : The code below will color an existing row yellow. I tested it by assigning a macro Hot-ket to the code; Ctrl-y. JSW Sub YellowRow() Code by Jow Was 'At cursor, select Row, color Yellow. 'Color Row Yellow. With Selection.EntireRow.Interior .ColorIndex = 6 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With

Re: Code to color an existing entire row at the current cursor location.

Sid,
I don't have a clue as to what you want to do. JSW Joe thanx again - i understand how to colour the row, but I want to automatically alternate - red, yellow, red, etc. I am using the code below to generate a list from several worksheets, the generated list can contain any mix of these sheets dependant on constantly changing values therein. Therefore the code you gave me must be referenced against the absolute row value - how? Public Sub BungsFitted() Application.ScreenUpdating = False Sheets("Report").Select FinalRow = Range("A65536").End(xlUp).Row Range("A2", "L" & FinalRow).Select Selection.ClearContents Sheets("AA").Select ' Find the last row of data FinalRow = Range("A65536").End(xlUp).Row ' Loop through each row For x = 2 To FinalRow ' Decide if to copy based on column K ThisValue = Range("K" & x).Value If ThisValue = "" Then Range("A" & x & ":M" & x).Copy Sheets("Report").Select NextRow = Range("A65536").End(xlUp).Row + 1 Range("A" & NextRow).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveCell.Select Sheets("AA").Select End If Next x Sheets("BB").Select ' Find the last row of data FinalRow = Range("A65536").End(xlUp).Row ' Loop through each row For x = 2 To FinalRow ' Decide if to copy based on column K ThisValue = Range("K" & x).Value If ThisValue = "" Then Range("A" & x & ":M" & x).Copy Sheets("Report").Select NextRow = Range("A65536").End(xlUp).Row + 1 Range("A" & NextRow).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Sheets("BB").Select End If Next x Application.CutCopyMode = False Sheets("Report").Select Range("A2").Select Cheers Sid

Sid this code will color only the data rows yellow & Red!

Sub blank()
'By Joe Was
'This code can be run from a form button or Hot-key option.
'If you move from one sheet to another the code may need to address
'the sheet names (The code is set to be active on the sheet receiving
'the data).
'This copies Yellow and Red rows to any list.
'The key row is "A" for the last row of wanted data
'their must be a value in Column "A" last row!

'Sid, Insert your copy table code here!

'(Your code here!)

'Any formats you want your new list to have, must be
'set on your copy to sheet, before you run the code below!
'So if you want your list to have field formats, set them
'in both rows 1 & 2 with additional code here.

'Your code here! Optional!)

'The code below will color rows 1 & 2.
'Then copy the formats to all the A through F rows on the sheet.
Range("A1:F1").Select
With Selection.Interior
.ColorIndex = 6
.PatternColorIndex = xlAutomatic
End With
Range("A2:F2").Select
With Selection.Interior
.ColorIndex = 3
.PatternColorIndex = xlAutomatic
End With
Range("A1:F2").Select
Selection.Copy
Range("A3:F65536").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'This code will remove any un-used colored rows, from the bottom
'of the sheet, to the last row in coloumn "A" that has data!
Dim myCell As Range
With Worksheets("Sheet1")
Set myCell = Range("A65536").End(xlUp).Offset(1, 0).Cells(1, 1)
.Range(myCell, .Cells(65536, 1)).Select
Selection.EntireRow.Delete
End With
Range("A1").Select
End Sub

JSW

Sorted it with "=MOD(ROW(),2)=0" in conditional formatting which I got from Aaron's site.
Many Thanx Aaron
Sid

If anyone is interested, I built a free addin called "Ledger Shade" that does this sort of thing. One of the features it has is that it can create ledger shading by automatically writing the necessary conditional format logic. You could use the logic in your macro or just use the addin it's up to you. Regards, Aaron The Excel Logic Page http://geocities.com/aaronblood

: I would like to alternate rows between 2 colours in a list. The rows are added to a list by a macro therefore I guess I need to reference to the row number but can't figure it out. Can anyone help please. : Thanx : Sid