CODEWITHSUNDEEP
excelvba
TkdKidSnake
TkdKidSnake

Reputation: 69

Change The Contents Of A Cell Depending On What Is Already In That Cell

does anyone know how to change the contents of a cell in a spreadsheet depending on what is found in the same cell and then change the colour scheme using VBA

For example

Cell Value = N/A#

New Cell Value Will Be = Not On Previous Report

Colour Scheme = Black & Bold Text on a yellow background

Or

Cell Value = Zero or the cell has nothing in so is blank

New Cell Value Will Be = No Update Provided On Previous Report

Colour Scheme = White & Bold Text on a red background

There are also other cell values and these are to be left intact.

Thanks in advance for any help provided it is most appreciated

http://www.mrexcel.com/forum/excel-questions/780048-how-do-you-change-contents-cell-depending-what-already-cell.html

Upvotes: 1

Views: 94

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Select the cells and run this:

Sub repair()
    Dim r As Range
    For Each r In Selection
        If r.Text = "#N/A" Then
            r.Value = "Not On Previous Report"
            r.Interior.ColorIndex = 27
            r.Font.FontStyle = "Bold"
        End If
        If r.Value = "" Or r.Value = 0 Then
            r.Value = "No Update Provided On Previous Report"
            r.Interior.ColorIndex = 3
            r.Font.FontStyle = "Bold"
            r.Font.ColorIndex = 2
        End If
    Next r
End Sub

Upvotes: 1

Related Questions