Code for coloring cells as that cell.........?

[FvdF]

Dear forum readers,

I have the following problem.

In the file I want to give each cell that has to different value than the cell in the previous column same row a color (blue).

In the example you see cell G41 has no color but has different value than F41.

Also, in A2 the correct number of colored cells must be displayed. This is now correct but column B should not be counted so that code needs to be changed.

So the current VBA code is almost correct but if there is a better code then this can be done, only I am not a code expert. So please provide complete then.

This is sheet DG-(28-16) and in sheet Data 28-16 there is the value which can be copied with copy & paste back into sheet DG-(28-16) for testing.

I am working with the Excel 2019 (NL) version, but ENG will also work I hope

With Regards,
FvdF

>>> use code - tags <<<

Sub Schema2816()

    Call UnprotectActiveSheet

'   KeepOnlyNeededRows

    Dim ws As Worksheet
    Dim Rng As Range
    Dim LastRow As Long

    Set ws = ActiveWorkbook.Sheets("DG-(28-16)")

    LastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row

    Set Rng = ws.Range("B4:B" & LastRow)

'   filter and delete all but header row
    With Rng
    
    .AutoFilter Field:=1, Criteria1:="<>*2022*"
    .Offset(0, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
  
    End With

'   turn off the filters
    ws.AutoFilterMode = False
  
'   MoveRows
  
    Rows("32:47").Cut
    Rows(18).Insert
  
  
'   Borders

    Range("A18:H33").BorderAround _
                LineStyle:=XlLineStyle.xlContinuous, _
                Weight:=xlMedium
              
  
    Range("B4:H4").Interior.Color = RGB(0, 255, 255)

'   ColorRowsNotEquelGOV
nColour = 7

Dim cl As Range

For Each cl In Range("B4:H" & Cells(Rows.Count, "B").End(xlUp).Row)
    If Not cl.Value Like "*GOV*" Then
        If WorksheetFunction.CountIf(Range(Cells(cl.Row, "B"), cl), cl.Value) = 1 Then
            nColour = nColour + 1
            cl.Interior.Color = RGB(0, 255, 255)
        End If
    End If
Next cl

    LastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
    ws.Range("A4:A" & LastRow).Formula = "=row()-3"
    ws.Range("A2") = nColour
 
    Call ProtectActiveSheet

'    Call PDFActiveSheetNoPrompt

End Sub

 

[BobBridges]

I haven't tried the program, but I'm confused by this statement:

If WorksheetFunction.CountIf(Range(Cells(cl.Row, "B"), cl), cl.Value) = 1...

Specifically, the part that says Cells(cl.Row, "B"). Does this correctly identify your target cell? When using that format (which is most of the time) I'm used to putting numbers in there for both the row and the column. If you want to use the A1 format (which I don't use much) I would expect it to say Cells("B" & cl.Row).

If you say it's pointing to the right cell, I'll believe you. But I'm doubtful.

 

[FvdF]

Dear BobBridges,

I don't have enough knowledge of programming myself. So I search the internet for what I think I need and hope it works.
This case is no different. I've come this so far, but I still have two issues that I want to solve. This must be done with the help of someone.
This is why I don't understand your comments, can you do something for me to adjust it or maybe make something completely new.
I would like to explain what this vba should do.

I would like to hear from you again.

With Regards,
FvdF

 

[BobBridges]

I'm more interested in teaching people to program, not so much in writing your program myself. It's perfectly ok that you don't yet know how, and we can work together until you do. But if you don't think you can learn, and would rather someone just write the program for you, I suggest you post the question again and hope that someone else answers this time. I'll try to remember not to reply, although I may forget .

 

[vletm]

BobBridges
Please, do not teach to duplicate thread - duplicated threads will close.
Better to teach to add more details and 'sell' this thread for others.

 

[vletm]

FvdF
Is somewhere expected results?
You've named ... only ... In the example you see cell G41 has no color but has different value than F41.

I tried to figure something.
I needed to modify cell F1-formula ( to filename ).
I cleaned two part of code .. protect and unprotect.
I added one button to cell D1 - it's like on/off -button.
It cleans color or it shows colors.
If all rows values from B to H are same then - no color.
If any rows value is different then there will be different colors.
Number of same values and color has connection per row.
Cell A1 shows number based colors.
Cell A2 shows number cells which has mark with color