Problem with Cell Colouring - VBA

Dinngz

New Member
Joined
Jan 28, 2025
Messages
11
Office Version
  1. Prefer Not To Say
Platform
  1. 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.

1738755235186.png


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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Check if this is what you need:

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)
    ws.Range("D4", ws.Cells(Rows.Count, Columns.Count)).Interior.ColorIndex = xlNone
    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 ws.Cells(matchCell.Row, categoryCell.Column).Value = "x" Then
                ws.Cells(matchCell.Row, categoryCell.Column).Interior.Color = RGB(0, 255, 0) ' Green color
            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

🤗
 
Upvote 0
Solution
Hi Mate,

Almost.. Whilst it now picks up the Value in A4, it only allows formatting of one cell. I want it to keep formatting or the cells that I set to colour. If that makes sense :)
 
Upvote 0
I don't understand.

According to your example, only one cell makes a cell with the conditions "1W PM", "2" and "x"

So which other cells do you want to highlight and why?

Could you explain the example clearly.
That is, an image before executing the macro and another image with the expected result.
 
Upvote 0
Cell A4 is the first condition... Cell A5 is the second condition..

User selects PM Name from Cell A5, They then select Week Number from Cell A5.. This is because I want to mark the selected PM completed.
If the user wants to mark the PM as completed, there must be a cell value of "x" for them to do so, as it has been planned for that week.

User selects the conditions, which then marks their selection as green. This is to remain green. If another selection is made, this adds another green box (still keeping the previous one green)

For arguments sake a user has selected and turned cell F4 green. They now want to make cell E2 green (with previous cell, F4, remaining green)

I hope this helps
 
Upvote 0
Remove this line from the macro:

ws.Range("D4", ws.Cells(Rows.Count, Columns.Count)).Interior.ColorIndex = xlNone

Try again
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,134
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top