Reputation: 1
I would like to improve my excel minutes sheet which I got from my predecessor.
In that excel sheet I have a button which enters some predefined values in the row of the cell I clicked in. There are some values that are entered for the columns that are predefined. A- index No B- abbreviation for a task, participant, decision etc. c- responsible person D- date
There are buttons to support the entry of data. If I press the T-Button (for task), a t appears in the active row (column B) and a certain layout is indicated. Likewise for the P-Button (participants). Although there are macros and some changes in vba coding; This all works fine!
Now I have changes I would like to implement:
The code looks like this:
Sub NewDecision()
Dim cell As Range
If ActiveSheet.AutoFilterMode Then
Selection.AutoFilter
End If
If Range("B4").Value = "" Then
Range("B4").Select
Else
Range("B3").End(xlDown).Offset(1, 0).Range("A1").Select
End If
Application.ScreenUpdating = False
ActiveCell.FormulaR1C1 = "D"
Application.Run "SetNewNumber"
Application.Run "CopyFormat"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "all"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
ActiveCell.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(0, -3).Range("A1").Select
Application.ScreenUpdating = True
For Each cell In Worksheets("Close").Range("A4:F33")
If cell = "" Then
cell.Interior.ColorIndex = 3
Else
cell.Interior.ColorIndex = xlNone
End If
Next
End Sub
I believe that the problem lays in this piece of code:
Sub CopyFormat()
Range("A4:J4").Copy
Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 10)).PasteSpecial Paste:=xlFormats
Cells(ActiveCell.Row, 2).Validation.InCellDropdown = False
ActiveCell.Offset(0, 1).Select
Application.CutCopyMode = False
I hope you can help me...Feel free to ask questions.
Bests
Upvotes: 0
Views: 161
Reputation: 1277
Turn Red:
With Range("SOME RANGE").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Remove Color:
With Range("SOME RANGE").Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Upvotes: 1