user3334425
user3334425

Reputation: 1

VBA colouring a cell and decolouring it

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

Answers (1)

MLDev
MLDev

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

Related Questions