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