Auto fill cell color for row

[thewiseguy]

Hi all,

I am trying to auto-fill cell color based on whether the value in column A is odd or even. Odd values would make the entire row (A-O) have no fill, and even values would make the entire row (A-O) have grey fill.

Can anyone provide any guidance?

Much appreciated.



/M

 

[Peter_SSs]

this worked as well

Just a few comments about that code, admittedly not knowing much about how your sheet will be used.

- If a text value happens to be entered in column A (like cell A6) in my sample, the code will error.
- If all the other values as shown in my sample are entered individually, then the colour will get applied correctly. However, assuming nothing else has been entered below A10, if you then delete that zero from cell A10, the colour will not be removed from row 10 even though that row no longer holds an even number.
- If multiple cells are altered at once (in most worksheets it is not uncommon for several cells to be selected & deleted, or for several values to be pasted or entered at once with Ctrl+Enter & in any of those circumstances the code will not update the colour for those rows.

All of the above can be overcome if they are, or become, an issue for you and you would prefer to use vba over Conditional Formatting.

I also point out that the Conditional Formatting that I suggested is not fool-proof either as a user might copy some data from elsewhere and paste it into column A (& possibly other columns too). That would destroy any Conditional Formatting that had been set up in those cells.

In summary, I am not championing either method as they both have potential drawbacks so you just need to choose the one that best suits your circumstances knowing those possible issues.

 

[Peter_SSs]

.. it's just that the 'CountLarge > 1' part has me confused.

If there is a link I could go to for more information, I'd appreciate it if you could let me know.

Using CountLarge in this code is just a very conservative precaution in case the user has changed more than 2,147,483,647 cells all at once.
If that had happened, using just Target.Cells.Count would cause an overflow error.
Ref: https://docs.microsoft.com/en-us/office/vba/api/excel.range.countlarge

But we do not want the script to activate if the user for example selected 1000 cells and tried to change them all at once we do not want the script to run. Like if the user tried to select 1000 cells and tried to clear them we would not want the script to run.

For the record, I disagree strongly with that. If the user changed 1000 cells in column A all at once, I would want the code to check them all and apply or remove the colour from each of those rows depending on the new value in column A.

 

[Rick Rothstein]

Just a few comments about that code, admittedly not knowing much about how your sheet will be used.

- If a text value happens to be entered in column A (like cell A6) in my sample, the code will error.
- If all the other values as shown in my sample are entered individually, then the colour will get applied correctly. However, assuming nothing else has been entered below A10, if you then delete that zero from cell A10, the colour will not be removed from row 10 even though that row no longer holds an even number.
- If multiple cells are altered at once (in most worksheets it is not uncommon for several cells to be selected & deleted, or for several values to be pasted or entered at once with Ctrl+Enter & in any of those circumstances the code will not update the colour for those rows.

All of the above can be overcome if they are, or become, an issue for you and you would prefer to use vba over Conditional Formatting.

Just so the OP has it if needed, here is what I believe to be a Change event procedure which addresses all of the concerns you raised above...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  On Error GoTo NotColumnA
  For Each Cell In Intersect(Intersect(Target, Columns("A")), ActiveSheet.UsedRange)
    With Intersect(Cell.EntireRow, Columns("A:O")).Interior
      If Not Cell.Value Like "*[!0-9]*" And Cell.Value Like "*[02468]" Then
        .ColorIndex = 48
      Else
        .ColorIndex = xlColorIndexNone
      End If
    End With
  Next
NotColumnA:
End Sub
[/td]
[/tr]
[/table]