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]

You should be able to use Conditional Formatting for that.
- Select A2:O??
- Home ribbon tab -> Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:- =AND(ISEVEN($A2),$A2<>"") -> Format... -> Fill tab -> Choose grey -> OK -> OK

 

[thewiseguy]

You should be able to use Conditional Formatting for that.
- Select A2:O??
- Home ribbon tab -> Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:- =AND(ISEVEN($A2),$A2<>"") -> Format... -> Fill tab -> Choose grey -> OK -> OK

so this is working to some extent however it is not filling based on the value in column A being odd or even. so a row that is grey is filling both an odd and even row in column A.

 

[Peter_SSs]

Are you sure you followed the instructions exactly? In particular, did you select from A2 to O?? with A2 being the active cell after the selection?

Here is my test sheet. Does this look how it should?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	4
3	3
4	56
5
6	abc
7	5
8	4
9	33
10	0
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =AND(ISEVEN($A2),$A2<>"")	Abc

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/16/2018  8:27:36 AM  EDT
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
IsEven = Target.Value Mod 2
If IsEven = 0 Then Cells(Target.Row, 1).Resize(, 15).Interior.ColorIndex = 48
If IsEven = 1 Then Cells(Target.Row, 1).Resize(, 15).Interior.ColorIndex = -4142
End If
End Sub

 

[thewiseguy]

Are you sure you followed the instructions exactly? In particular, did you select from A2 to O?? with A2 being the active cell after the selection?

Here is my test sheet. Does this look how it should?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	4
3	3
4	56
5
6	abc
7	5
8	4
9	33
10	0
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =AND(ISEVEN($A2),$A2<>"")	Abc

got it. i wasn't starting at row to so had to make the adjustment. thank you again.

 

[thewiseguy]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/16/2018  8:27:36 AM  EDT
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
IsEven = Target.Value Mod 2
If IsEven = 0 Then Cells(Target.Row, 1).Resize(, 15).Interior.ColorIndex = 48
If IsEven = 1 Then Cells(Target.Row, 1).Resize(, 15).Interior.ColorIndex = -4142
End If
End Sub

this worked as well - thanks for the edu!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

this worked as well - thanks for the edu!

 

[TotallyConfused]

If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub

Hello My Aswer Is This

I'm fairly new to VBA so I was wondering if I may impose on you for a moment to ask a question. I'm wondering what the command of 'CountLarge' does? (See post #7 ) I looked in the HELP files, and found 'Count' and also 'Large' but couldn't find anything that looked like 'CountLarge'. I understand that 'IF' statement is a way to break out of the subroutine, 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. THANK YOU for any help you may be able to offer.

TotallyConfused (a state I'm in most of the time)

 

[My Aswer Is This]

Well this is a sheet change event script.
So if the user changes a cell value we want something to happen.
But we only want one cell change to occur.

So if Range("A1").value changes do this.

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.

Hope that is clear.