Providing message if red coloured cells found in specific columns

[djdbg1]

I have a worksheet, "Test Data Request" which has numerous data validations. I have had to enable this worksheet to allow "paste as link" to work as some business areas will be pasting in data from a source system extract rather than manually populating each field. As this will overwrite the data validations, i have used conditional formatting to change a cell to red fill if it doesn't match one of the data validation options.
In case the red cells are not seen, i want to add a button that would confirm if everything in that worksheet ("Test Data Request"), from rows 3 and below was good, i.e. no red cells or if "Invalid Data or Required Data Missing - Please fix before proceeding".
The script below looks for red cells by referencing $A$2 (as it will always be red as its a mandatory header i have filled red).
However i want to have this button on a different worksheet. I can't figure out where to reference worksheet "Test Data Request" and where to define range is ROW3 and above.
Finally not all cells are mandatory. Some may appear red as blank, but they are optional, so i also need to confirm which columns to check. I would really appreciate of anyone could offer suggestion how to get the below script to do as i need.

1. refer to sheet sheet "Test Data Request" rather than run in current sheet
2. Ignore rows 1 and 2 and only check rows 3 and below.
3. specify columns to check.

TIA

Sub SumCountByConditionalFormat()
    Dim indRefColor As Long
    Dim cellsColorSample As Range
    Dim cntRes As Long
    Dim sumRes
    Dim cntCells As Long
    Dim indCurCell As Long

    On Error Resume Next

    cntRes = 0
    sumRes = 0

    cntCells = Selection.CountLarge

    Set cellsColorSample = Range("$A$2")

    If Not (cellsColorSample Is Nothing) Then
       indRefColor = cellsColorSample.Cells(1, 1).DisplayFormat.Interior.Color

       For indCurCell = 1 To (cntCells)
            If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then
               cntRes = cntRes + 1
               sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes)
            End If
        Next
        MsgBox "Invalid Data or Required Data Missing - Please fix before proceeding"
    End If
End Sub

 

[NdNoviceHlp]

Hi djdgb. It's raining and it seems I have some time on my hands. Not sure if your still looking for a solution but this trials OK. Set the column using the ColNum variable as an integer to represent the column (ie. Column "B" is 2). If you want to search more than 1 column, the code is easily adjustable. HTH. Dave

Sub SumCountByConditionalFormat()
Dim indRefColor As Long, LastRow As Integer, MyRng As Range, Rng As Range
Dim CntRes As Integer, ColNum As Integer

ColNum = 2 ' Column "B"
'set rng to search
With Sheets("Test Data Request")
LastRow = .Cells(.Rows.Count, ColNum).End(xlUp).Row
Set MyRng = .Range(.Cells(3, ColNum), .Cells(LastRow, ColNum))
'set reference colour
indRefColor = .Cells(2, 1).Interior.Color
End With
'search rng for reference colour
For Each Rng In MyRng
If Rng.Interior.Color = indRefColor Then
CntRes = CntRes + 1
End If
Next Rng
If CntRes > 0 Then
MsgBox "Invalid Data or Required Data Missing - Please fix " _
                                      & CntRes & "cells before proceeding"
Else
MsgBox "OK"
End If
End Sub

ps. note the code assumes that the coloured cells also contain data

 

[djdbg1]

Hi djdgb. It's raining and it seems I have some time on my hands. Not sure if your still looking for a solution but this trials OK. Set the column using the ColNum variable as an integer to represent the column (ie. Column "B" is 2). If you want to search more than 1 column, the code is easily adjustable. HTH. Dave

Sub SumCountByConditionalFormat()
Dim indRefColor As Long, LastRow As Integer, MyRng As Range, Rng As Range
Dim CntRes As Integer, ColNum As Integer

ColNum = 2 ' Column "B"
'set rng to search
With Sheets("Test Data Request")
LastRow = .Cells(.Rows.Count, ColNum).End(xlUp).Row
Set MyRng = .Range(.Cells(3, ColNum), .Cells(LastRow, ColNum))
'set reference colour
indRefColor = .Cells(2, 1).Interior.Color
End With
'search rng for reference colour
For Each Rng In MyRng
If Rng.Interior.Color = indRefColor Then
CntRes = CntRes + 1
End If
Next Rng
If CntRes > 0 Then
MsgBox "Invalid Data or Required Data Missing - Please fix " _
                                      & CntRes & "cells before proceeding"
Else
MsgBox "OK"
End If
End Sub

ps. note the code assumes that the coloured cells also contain data

Thank you very much, I am now glad it is raining.
I have created a button for that script and ran it but outcome not as expected. Is it possible that if cells are RED due to conditional formatting it wont work? When i ran it, i ensured there were red cells in column B, but these are red due to conditional formatting, The message comes back OK. However when i manually fill red, then it comes back with "Invalid Data or Required Data Missing - Please fix..."
Also, some cells are RED through conditional formatting because they are blank and it needs to pick up on this too. (P.S i checked same colour used in conditional formatting as used in cell i am referring to, both are RED).
Thanks
Danny

 

[djdbg1]

Thank you very much, I am now glad it is raining.
I have created a button for that script and ran it but outcome not as expected. Is it possible that if cells are RED due to conditional formatting it wont work? When i ran it, i ensured there were red cells in column B, but these are red due to conditional formatting, The message comes back OK. However when i manually fill red, then it comes back with "Invalid Data or Required Data Missing - Please fix..."
Also, some cells are RED through conditional formatting because they are blank and it needs to pick up on this too. (P.S i checked same colour used in conditional formatting as used in cell i am referring to, both are RED).
Thanks
Danny

Also i have no idea how to amend column reference. I need to verfiy no red cells, blank or other wise in columns;
1 2 3 5 6 7 12 19 23 27 29 30 31 33 34 35 37 41 43 54

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

 

[NdNoviceHlp]

Hi Danny. The red cells need to have values in them in order to set the LastRow search parameter. If there is no value (and the cell(s) may only be coloured) the lastrow of the search data has to be set differently. Dave
OK seen your last post.... So the cells can be blank and red?

 

[djdbg1]

Hi Danny. The red cells need to have values in them in order to set the LastRow search parameter. If there is no value (and the cell(s) may only be coloured) the lastrow of the search data has to be set differently. Dave
OK seen your last post.... So the cells can be blank and red?

Yes, and the red cells that are empty in these designated columns are compulsory, so need to be flagged if missed.

 

[NdNoviceHlp]

OK this seems to work. You must have some data (value or colour) in row 1 somewhere. The used range doesn't care if a value is present ie. it can be colour only. Dave

Sub SumCountByConditionalFormat()
Dim indRefColor As Long, LastRow As Integer, MyRng As Range, Rng As Range
Dim CntRes As Integer, ColNumArr As Variant, Cnt As Integer
'set reference colour to "A2"
indRefColor = Sheets("Test Data Request").Cells(2, 1).Interior.Color

'set columns to search
ColNumArr = Array(1, 2, 3, 5, 6, 7, 12, 19, 23, 27, 29, 30, 31, 33, 34, 35, 37, 41, 43, 54)
'set lastrow of search
'****Row 1 somewhere has to have colour/value
LastRow = Sheets("Test Data Request").UsedRange.Rows.Count

'loop columns
For Cnt = LBound(ColNumArr) To UBound(ColNumArr)
'set rng to search row 3 to lastrow for each column
With Sheets("Test Data Request")
Set MyRng = .Range(.Cells(3, ColNumArr(Cnt)), .Cells(LastRow, ColNumArr(Cnt)))
End With
'search rng for reference colour
For Each Rng In MyRng
If Rng.Interior.Color = indRefColor Then
CntRes = CntRes + 1
End If
Next Rng
Next Cnt

If CntRes > 0 Then
MsgBox "Invalid Data or Required Data Missing - Please fix " _
                                      & CntRes & "cells before proceeding"
Else
MsgBox "OK"
End If
End Sub

 

[djdbg1]

OK this seems to work. You must have some data (value or colour) in row 1 somewhere. The used range doesn't care if a value is present ie. it can be colour only. Dave

Sub SumCountByConditionalFormat()
Dim indRefColor As Long, LastRow As Integer, MyRng As Range, Rng As Range
Dim CntRes As Integer, ColNumArr As Variant, Cnt As Integer
'set reference colour to "A2"
indRefColor = Sheets("Test Data Request").Cells(2, 1).Interior.Color

'set columns to search
ColNumArr = Array(1, 2, 3, 5, 6, 7, 12, 19, 23, 27, 29, 30, 31, 33, 34, 35, 37, 41, 43, 54)
'set lastrow of search
'****Row 1 somewhere has to have colour/value
LastRow = Sheets("Test Data Request").UsedRange.Rows.Count

'loop columns
For Cnt = LBound(ColNumArr) To UBound(ColNumArr)
'set rng to search row 3 to lastrow for each column
With Sheets("Test Data Request")
Set MyRng = .Range(.Cells(3, ColNumArr(Cnt)), .Cells(LastRow, ColNumArr(Cnt)))
End With
'search rng for reference colour
For Each Rng In MyRng
If Rng.Interior.Color = indRefColor Then
CntRes = CntRes + 1
End If
Next Rng
Next Cnt

If CntRes > 0 Then
MsgBox "Invalid Data or Required Data Missing - Please fix " _
                                      & CntRes & "cells before proceeding"
Else
MsgBox "OK"
End If
End Sub

That's great Dave, very much appreciate your time and support. Sadly it still doesn't recognise where a cell is red due to conditional formatting. It only recognises when i manually make a cell red in one of the desired columns. It is the same RED selected for conditional formatting too, i checked same hex and Red 255, with green and blue 0. Again, thank you for sparing your time.

 

[NdNoviceHlp]

Hmmm. Trial changing this line of code...

If Rng.Interior.Color = indRefColor Then
'to
If Rng.DisplayFormat.Interior.Color = indRefColor Then

Dave

 

[djdbg1]

Hmmm. Trial changing this line of code...

If Rng.Interior.Color = indRefColor Then
'to
If Rng.DisplayFormat.Interior.Color = indRefColor Then

Dave

And we have a winner! You absolute legend!!!! Thank you so much.