Update Cell In Relation To Input Of Another Cell

jj72uk · Mar 25th 2008

I will try to be as clear and concise as possiable and adhear to the forum rules. I'm not a excel wizz, though I know the bascics and am willing to learn, if i dont use the right terminology to describe my problem please forgive me.

Here's what i am trying to do:

I have a list in column A, in thise list there are repeated words eg the word sugar is repeated in A23, A45, A56 etc.

In column B i have condition format so when i type a 'x' it turns the cell color from red to green.

What i am trying to do is if i enter the 'x' in column B in relation to the word - in this case sugar - it will automatically enter a x in all the other cells in B that has the word in there corresponding A column that matches the orginal 'x'

So for example if the word sugar is repeated in A23, A45 and A56 regaredless of where i enter the 'x' in the b column, weather its B23, B45 or B56 then cells i didnt manually enter the x will auto update in corresondance to the word...

I hope i have been clear and someone can help.

regards

yjoshi · Mar 25th 2008

Re: Auto Updating Cell In Relation To Input Of Another Cell

Welcome to OZGrid...
while your question is very clear... i am not sure what you want to achieve by getting X against all these repeated values?

Is it for some kind of conditional formatting?

Fencliff · Mar 25th 2008

Re: Auto Updating Cell In Relation To Input Of Another Cell

Here ya go. Right-click the worksheet tab of the sheet you want to use this on, and paste the following code into the opening window:

Code

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim rngIntersect    As Range
    Dim rngList         As Range
    Dim rngCell         As Range
    Dim vCompare        As Variant
    
    'Validate that we are only editing one cell. Otherwise checking
    'Target.Value will throw an error
    If Target.Cells.Count = 1 Then
    
        'No reason to process further if we are not marking x. It is
        'cheaper to check this than to do a range intersect
        If LCase(Target.Value) = "x" Then
    
            'Check whether we are adding to column B...
            On Error Resume Next
            Set rngIntersect = Application.Intersect(Target, Me.Range("B:B"))
            On Error GoTo 0
            
            '...if Target is in column B
            If Not rngIntersect Is Nothing Then
                
                'Get list of all non-empty cells in column A
                Set rngList = Me.Range("A:A").SpecialCells(xlCellTypeConstants)
                
                'Get the value in column A where we have added the x
                vCompare = Target.Offset(0, -1).Value
                
                Application.EnableEvents = False
                Application.ScreenUpdating = False
                
                'Loop through all cells in Column A
                For Each rngCell In rngList
                    
                    'If the cell value matches comparison value
                    If rngCell.Value = vCompare Then
                        'Enter x to column B
                        rngCell.Offset(0, 1).Value = "x"
                    End If
                Next rngCell
                
                Application.ScreenUpdating = True
                Application.EnableEvents = True
                
            End If
        End If
    End If
End Sub

Display More

Please take a moment to read through the comments in the code to understand what is happening.

jj72uk · Mar 25th 2008

Re: Auto Updating Cell In Relation To Input Of Another Cell

Its simply a checklist of various items located at various places.

In reality its a spread sheet of skills you can capture within a computer game i play, these skills can be captured at various locations within the game, but once you've captured the skill yo dont need to capture it again.

So the skill may be repeated 5 times in the list at 5 various locations, so with putting the 'x' next to it - meaning its been captured - i'm trying so all the instances of that skill have a 'x' next to them so the user of the spreadsheet will not go out and try to re capture, and knows for there records they have gotten it already.

There is some conditional formatting in there, but all that does it chnage the cell color from red to green weather i has a 'x' in it or not.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]fencliff that looks amazing..give me a few mins and i will try it![hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]fencliff that looks amazing..give me a few mins and i will try it![hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]wow!!! that works sooo good...talk about a god send after a easter weekend of head scratching!! thank you so much..

Just to enquire as i know it will be an issue...how about using this across 3 sheets? As there would be some repition across them to?

Update Cell In Relation To Input Of Another Cell

Participate now!

Share