Dinngz
New Member
- Joined
- Jan 28, 2025
- Messages
- 11
- Office Version
- Prefer Not To Say
- Platform
- Windows
Hi All,
Looking for some expertise with the current VBA code that i have.
I am generating a sheet where if two Cell A4 = a line in Column C, then it is to check the value in cell A5 and match it along row 3.
If there is a match with both values, and a cell on that row contains an x, on click of a button it is to turn green.
I have this working on the first row, however if i select a value that matches other rows it does not work.
Here is the VBA code I am using:
Many Thanks
Looking for some expertise with the current VBA code that i have.
I am generating a sheet where if two Cell A4 = a line in Column C, then it is to check the value in cell A5 and match it along row 3.
If there is a match with both values, and a cell on that row contains an x, on click of a button it is to turn green.
I have this working on the first row, however if i select a value that matches other rows it does not work.
Here is the VBA code I am using:
VBA Code:
Sub CheckAndFormatCell()
Dim ws As Worksheet
Dim matchCell As Range
Dim categoryCell As Range
Dim valueA4 As String
Dim valueA5 As String
Set ws = ThisWorkbook.Sheets("PMCompletion") ' Change "Sheet1" to your sheet name
valueA4 = ws.Range("A4").Value ' Get the value in A4
valueA5 = ws.Range("A5").Value ' Get the value in A5
' Step 1: Match value in A4 to a cell in column C
Set matchCell = ws.Columns("C:C").Find(What:=valueA4, LookIn:=xlValues, LookAt:=xlWhole)
If Not matchCell Is Nothing Then
' Step 2: Use value in A5 to find a match in row 3
Set categoryCell = ws.Rows("3:3").Find(What:=valueA5, LookIn:=xlValues, LookAt:=xlWhole)
If Not categoryCell Is Nothing Then
' Step 3: Check the cell below the matched cell in row 3 for "x"
If categoryCell.Offset(1, 0).Value = "x" Then
categoryCell.Offset(1, 0).Interior.Color = RGB(0, 255, 0) ' Green color
Else
categoryCell.Offset(1, 0).Interior.ColorIndex = xlNone ' Clear formatting if no "x"
End If
Else
MsgBox "No match found in row 3 for the value in A5.", vbInformation
End If
Else
MsgBox "No match found in column C for the value in A4.", vbInformation
End If
End Sub
Many Thanks