Colour a cell based upon certain logic.

[funkychunkymunky]

Hello All,

I am very new to VBA (Currently Day 3) and I have a problem.

I am trying to create a file that validates data. If the data is incorrect, it is highlighted in yellow. Following on from that I would like to hide all rows that have no incorrect data. This will make it easier for the people who have the fun job of correcting the data.

Most of the time i have an excel spread sheet that includes the list of acceptable fields for which I can go back and compare to.

From trawling through the internet and a couple of books I found the following formula, The one highlighted in Blue is what I want but it does not work with the second macro to hide the un highlighted columns as it does not recognise them as highlighted.

Is there any way to put the blue highlighted code in a similar format to the green highlighted code to the macro in purple works?

Sub HighlightIncorrectE1()


Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Raw_E1")


ws.Select
Dim FinalRow As Long
FinalRow = ws.Cells(Rows.Count, 1).End(xlUp).Row


'Highlight any data that does not match using conditional formatting
With Range("A2:A" & FinalRow)
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=COUNTIF(tmhncs_ids!$A:$A,A2) = 0"
.FormatConditions(1).Interior.Color = RGB(255, 255, 0)
Rows.Hidden = False
End With


'Highlight a field not using Conditional Formatting.
Set Rng = Range("C2:C" & FinalRow)
For Each cell In Rng
If cell.Value <> 1 Then cell.Interior.Color = RGB(255, 255, 0)
Next

End Sub

Option Explicit

Sub HideUncoloredRows()
Dim startColumn As Integer
Dim startRow As Integer


Dim totalRows As Integer
Dim totalColumns As Integer


Dim currentColumn As Integer
Dim currentRow As Integer


Dim shouldHideRow As Integer


startColumn = 1 'column A
startRow = 1 'row 1
totalRows = Worksheets("Raw_E1").Cells(Rows.Count, startColumn).End(xlUp).Row


For currentRow = totalRows To startRow Step -1
shouldHideRow = True
totalColumns = Worksheets("Raw_E1").Cells(currentRow, Columns.Count).End(xlToLeft).Column
'for each column in the current row, check the cell color
For currentColumn = startColumn To totalColumns
'if any colored cell is found, don't hide the row and move on to next row
If Not Worksheets("Raw_E1").Cells(currentRow, currentColumn).Interior.ColorIndex = -4142 Then
shouldHideRow = False
Exit For
End If
Next


If shouldHideRow Then
'drop into here if all cells in a row were white
Worksheets("Raw_E1").Cells(currentRow, currentColumn).EntireRow.Hidden = True
End If
Next
End Sub


Thank you in advance for all your help and suggestions. And please remember my knowledge of vba is very minimal

 

[JoeMo]

When you color a cell using conditional formatting you need to test it to see if it meets the format condition that causes it to be colored, and if so hide it. If the color is conditional then Cell.Interior.ColorIndex = xlColorIndexNone which is why your row-hiding procedure is not doing what you want it to do.

 

[funkychunkymunky]

Sorry Joe, I dont really understand your response - i am afraid it is too technical for me. I have never used VBA before.

I did test it, the conditional formatting does what i want, it colours the cell when the information does not match the prescribed list. However, when i use the conditional formatting to colour the cell (blue) compared to the other thing in green, the hide cells macro doesnt recognise these fields as being coloured so just hides them.

So I think I need to know how to colour the cell using the blue method and a prescribed list as opposed to the green one.

Is that any clearer?

P.S. I dont want to hide the cell if it is coloured - I want to show it and hide everything else.

 

[JoeMo]

Sorry Joe, I dont really understand your response - i am afraid it is too technical for me. I have never used VBA before.

I did test it, the conditional formatting does what i want, it colours the cell when the information does not match the prescribed list. However, when i use the conditional formatting to colour the cell (blue) compared to the other thing in green, the hide cells macro doesnt recognise these fields as being coloured so just hides them.

So I think I need to know how to colour the cell using the blue method and a prescribed list as opposed to the green one.

Is that any clearer?

P.S. I dont want to hide the cell if it is coloured - I want to show it and hide everything else.

In short, a cell that is colored via conditional formatting is seen as having no color by Excel. So rather than testing to see if the Interior of the cell has a color you need to identify the cell by seeing if the condition you set to color it is met. Alternatively, color the cells that meet your condition using:
If Cell meets condition than cell.interior.color = vbBlue .....

 

[funkychunkymunky]

Thanks very much for trying to help, I still dont understand though unfortunately but thanks for trying :D