Color in cell based on data using vba

Even

Board Regular
Joined
Jan 1, 2013
Messages
58
Office Version
  1. 365
Platform
  1. Windows
Hi there,
I am using conditional formatting, however, somehow it all gets messed up when I input new data and sort them. So I need a vba code to fill the cells with certain colors.
1. All the cells in column E that have a value between 0 and 92 should have the color code 10805247 in them.
And
2. All the cells that have 🗸 in range F3:S500 should have the color code 8246183 in them.

And this need to happen without activating a module. The cells should be filled when the condition is met.

Is this possible without conditional formatting?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,106
Office Version
  1. 365
  2. 2010
This might work...

Code:
Sub SetColors()
Dim lr As Long, i As Long, j As Long
lr = Cells(Rows.Count, "E").End(xlUp).Row
 For i = 1 To lr
 If Cells(i, "E") >= 0 And Cells(i, "E") <= 92 and not isempty(cells(i, "E")) Then
  Cells(i, "E").Interior.Color = 10805247
 End If
 Next i
 For i = 3 To 500
  For j = 5 To 19
  If Cells(i, j) = ChrW(252) Then
   Cells(i, j).Interior.Color = 8246183
  End If
  Next j
  Next i
End Sub
 
Solution

Even

Board Regular
Joined
Jan 1, 2013
Messages
58
Office Version
  1. 365
Platform
  1. Windows
I can use this even though it needs to be triggered by attaching it to a box or something to activate. Thank you so much!
 

Forum statistics

Threads
1,141,483
Messages
5,706,649
Members
421,460
Latest member
Taamrak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top