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

[TkdKidSnake]

Hi All,

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 backgroud

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


Thanks in advance for any help provided it is most appreciated.

 

[alansidman]

Crosspost: How Do You Change The Contents Of A Cell Depending On What Is Already In That Cell

 

[wigi]

For example, for the active cell:

Sub wigi()

    With ActiveCell
    
        If .Value = CVErr(xlErrNA) Then
            .Value = "Not On Previous Report"
            .Font.Bold = True
            .Font.Color = 0
            .Interior.ColorIndex = 6
        End If
    
    End With

End Sub

 

[TkdKidSnake]

Thanks i was in the process of doing this but as it was posted on this site first I have had to do it after the original post!

Crosspost: How Do You Change The Contents Of A Cell Depending On What Is Already In That Cell

 

[TkdKidSnake]

This has also been posted on the following sites

excel - Change The Contents Of A Cell Depending On What Is Already In That Cell - Stack Overflow
How Do You Change The Contents Of A Cell Depending On What Is Already In That Cell

Hi All,

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 backgroud

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


Thanks in advance for any help provided it is most appreciated.

 

[wigi]

5 hours without a reply on a Sunday morning seems to necessitate cross-posting?
Posting a question on 3 forums, what?
Maybe posting at Ozgrid.com is a possibility?

Anyway, I have given an answer above. Please make sure you close the other topics asap since the question is / will be solved here.
Thanks.

 

[TkdKidSnake]

Thank you for your response, can this also be done for an array of data i.e. in a loop - my data appears in column J from cell J4 and can vary in length

For example, for the active cell:

Sub wigi()

    With ActiveCell
    
        If .Value = CVErr(xlErrNA) Then
            .Value = "Not On Previous Report"
            .Font.Bold = True
            .Font.Color = 0
            .Interior.ColorIndex = 6
        End If
    
    End With

End Sub

 

[TkdKidSnake]

Hi Wigi,

This was posted in the UK less than 30 mins ago so it must be either the server time or time difference between countries.

Apologies for the misunderstanding

 

[wigi]

The topic at StackOverflow was created shortly, I agree.
The one at ExcelForum... not so sure. I see 6:11 AM as the time.
Even if it's all done in half an hour, why??? The kind of question you ask is usually (non-weekend days) solved in half an hour at most.
In any case, I do not agree with this kind of posting behavior, that's clear.
I see that you refuse to update the topics at StackOverflow and ExcelForum.
I will not provide any more help to you, sorry, but I bet you will understand.