Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

Change value in one cell based on value in another cell

I have a speadsheet where I need to change the value of the cell in Column C, based on the value of the cell in Column D.

Column C cells have a value of Medium.  I need to change that cell value to Medium Red if the value of the cell in Column D has a value that ends in 67.

I've attached a sample of the original file, and a sample of the desired results file.
DesiredSpreadsheet.xlsm
OriginalSpreadsheet.xlsm
0
mato01
Asked:
mato01
  • 2
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Enter this formula in C2 and copy down

="MEDIUM"&IF(RIGHT(D2,2)="67"," RED","")
0
 
mato01Author Commented:
Can you convert this statement to vba.

Sometimes there could be more than MEDIUM in the cell, and this method would require me to handle the file.
0
 
Saqib Husain, SyedEngineerCommented:
Sub red67()
    Dim cel As Range
    For Each cel In Range("C:C")
        If Right(cel.Offset(, 1), 2) = "67" Then
            If Right(cel, 4) <> " RED" Then
                cel = cel & " RED"
            End If
        End If
    Next cel
End Sub
0
 
mato01Author Commented:
Had to change  a little, so that it was specific to MEDIUM, but works perfectly.  As always thanks a lot.

Sub red67()
    Dim cel As Range
    For Each cel In Range("C:C")
        If cel = "MEDIUM" Then
            If Right(cel.Offset(, 1), 2) = "67" Then
                If Right(cel, 4) <> "RED" Then
                    cel = cel & " RED"
                End If
            End If
        End If
    Next cel
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now