Change cell value within a row depending on other cell values within same row

[cameron213]

I am creating a macro enable spreadsheet to help me keep track of support tickets that I am currently working on in my engineering position.

I have 16 columns that provide information about each support ticket, such as Date, Serial Number of the device, Device location and so forth. A new row is a new support ticket.

Let's say we are working with row 50.

Column E represents the status of the device for which the ticket has been opened. Such as Deployed, Configure, and Floor Install.

Column F represents the device type such as Exalogic, and Exadata

Column K represents whether or not I need to create a special ticket depending on the value of E "OR" the value of F. Column K's values can be either yes or no.


This is what I have thus far and it does work with the column E range.


Dim rng As Range
Dim cellE
Dim cellF

If Not Intersect(Range("E12:E100"), Target) Is Nothing Then
Application.EnableEvents = False

For Each rng In Intersect(Range("E12:E100"), Target)

Set cellE = Range("E" & rng.Row)
Set cellK = Range("K" & rng.Row)

If Range("E" & rng.Row) = "Configure" Then
Range("K" & rng.Row) = "No"
ElseIf Range("E" & rng.Row).Value Like "*Floor*" Then
Range("K" & rng.Row) = "No"

Else
Range("K" & rng.Row) = ""
End If

Next rng
Application.EnableEvents = True

End If


However, if I try adding in the F range, nothing works:



If Not Intersect(Range("E12:E100"), Range("F12:F100"), Target) Is Nothing Then
Application.EnableEvents = False

For Each rng In Intersect(Range("E12:E100"), Range("F12:F100"), Target)

Set cellE = Range("E" & rng.Row)
Set cellF = Range("F" & rng.Row)
Set cellK = Range("K" & rng.Row)

If Range("E" & rng.Row) = "Configure" Then
Range("K" & rng.Row) = "No"
ElseIf Range("E" & rng.Row).Value Like "*Floor*" Then
Range("K" & rng.Row) = "No"
ElseIf Range("F" & rng.Row).Value Like "*Exa*" Then
'Range("K" & rng.Row) = "Not Needed"
Else
Range("K" & rng.Row) = ""
End If

Next rng
Application.EnableEvents = True

End If


Any help is greatly appreciated

[sassora]

Is the point that Range("E12:E100") never intersects Range("F12:F100")?

Do you want something like, Intersect(Union(Range("E12:E100"), Range("F12:F100")), Target) ? Don't know if this works, just thinking logically.

sassora