vba to change cell color based on date

**scouse13** · 08-16-2020, 06:22 PM

Hi guys what i need is a bit of vba code that can change a range of cells backcolor if an adjacent cell contains a date.

i.e cells A1 to A55 and B1 to B55, if say B1 contains a date A1 turns GREEN, and if B15 contains a date A15 turns GREEN, but for the code to check all cells in B and change the A adjacent cell in A if the B column cell contains a date, i have tried conditional formatting, but would prefer using vba.

hope this is clear

scouse13

**nankw83** · 08-16-2020, 06:32 PM

Hi scouse13 ... Something like this ?

Sub test()

For Each cell In Range("B1", Range("B" & Rows.Count).End(3))
   If IsDate(cell) Then cell.Offset(, -1).Interior.Color = vbGreen
Next

End Sub

**scouse13** · 08-16-2020, 07:01 PM

sorry mate got confused, i am copying data to a sheet, so the dates will be in the copied cells, ie sheet2(sheet were data is copied to) range A1-A50, the code needs to check these cells and change green if they contain a date, sorry for the confusion

**vba_php** · 08-16-2020, 06:34 PM

what about this?

dim r as range
   for each r in range("b1", range("b1").end(xldown))
      if isdate(trim(r)) then
         r.Offset(0,-1).Interior.Color = rgb(0,255,0)
      end if
   next r

https://docs.microsoft.com/en-us/off...range.interior

**nankw83** · 08-16-2020, 07:04 PM

Ok, how about now ?

Sub test()

With Sheets("Sheet2")
   For Each cell In .Range("A1", .Range("A" & Rows.Count).End(3))
      If IsDate(cell) Then cell.Interior.Color = vbGreen
   Next
End With

End Sub

**scouse13** · 08-16-2020, 08:08 PM

thanks for that, it works fine and i figured out how to change the font color as well, 1 more question if i may, i need to the code to check different columns, but not in order it needs to check column B and F nd i AND P, IS THIS POSSIBLE, CHANGING FONT COLOR AS WELL

**nankw83** · 08-17-2020, 02:17 PM

Hi scouse13,

Try the below code ...

Sub test()

Dim cell As Range, Rg As Range, r$

With Sheets("Sheet2")
   For x = 1 To 3
   r = Choose(x, "B", "F", "P")
   Set Rg = Range(.Cells(1, r), .Cells(Rows.Count, r).End(3))
      For Each cell In Rg
         If IsDate(cell) Then cell.Interior.Color = vbGreen: cell.Font.Color = vbRed
      Next
   Next
End With

End Sub

vba to change cell color based on date

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

vba to change cell color based on date

Re: vba to change cell color based on date

Re: vba to change cell color based on date

Re: vba to change cell color based on date

Re: vba to change cell color based on date

Re: vba to change cell color based on date

Re: vba to change cell color based on date

Thread Information

Users Browsing this Thread

Similar Threads

How to make a cell adaptively change color based on its date?

VBA Code to Change Cell Color Based On the Cell Date/Value (like conditional formatting)

Change cell color based on date

change cell color based on last edited date

Cell color change based on date

cell color change based on due date

[SOLVED] Change cell color based on date

Bookmarks

Bookmarks

Posting Permissions