Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

Change value in one cell based on value in another cell

Posted on 2012-03-17
Medium Priority
185 Views
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.
0
Question by:mato01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 43

Expert Comment

ID: 37734051
Enter this formula in C2 and copy down

="MEDIUM"&IF(RIGHT(D2,2)="67"," RED","")
0

Author Comment

ID: 37734063
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

LVL 43

Accepted Solution

Saqib Husain, Syed earned 800 total points
ID: 37734075
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

Author Closing Comment

ID: 37734109
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

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Suggested Courses
Course of the Month9 days, 13 hours left to enroll