En un rango de celdas para el día y mes, poner en rojo la celda con letra blanca, el día actual
Tengo esta macro :
Private Sub Worksheet_Calculate()
'Por.Dante Amor
Application.EnableEvents = False
Set h = Sheets("Hoja1")
mes = Format(Date, "mmmm")
dia = Day(Date)
Set b = h.Range("C4:C15").Find(mes, lookat:=xlWhole)
If Not b Is Nothing Then
f = b.Row
Set b = h.Range("D2:AH2").Find(dia, lookat:=xlWhole)
If Not b Is Nothing Then
c = b.Column
For i = 4 To f
For j = 4 To 34
If i = f And j = c Then
Exit For
End If
conta = conta + h.Cells(i, j)
Next
Next
ventatotal = h.[B4]
Cells(f, c) = ventatotal - conta
End If
End If
Application.EnableEvents = True
End Sub
Se podría añadir que en la celda del día salga en rojo la celda con letra blanca, en el día que se abra la hoja
1 respuesta
Respuesta de Dante Amor
1
Te anexo la macro actualizada
Private Sub Worksheet_Calculate()
'Por.Dante Amor
Application.EnableEvents = False
Set h = Sheets("Hoja1")
'limpiar colores
h.Range("D4:AH15").Interior.ColorIndex = xlNone
h.Range("D4:AH15").Font.ColorIndex = xlAutomatic
'
mes = Format(Date, "mmmm")
dia = Day(Date)
Set b = h.Range("C4:C15").Find(mes, lookat:=xlWhole)
If Not b Is Nothing Then
f = b.Row
Set b = h.Range("D2:AH2").Find(dia, lookat:=xlWhole)
If Not b Is Nothing Then
c = b.Column
For i = 4 To f
For j = 4 To 34
If i = f And j = c Then
Exit For
End If
conta = conta + h.Cells(i, j)
Next
Next
ventatotal = h.[B4]
h.Cells(f, c) = ventatotal - conta
h.Cells(f, c).Interior.ColorIndex = 3
h.Cells(f, c).Font.ColorIndex = 2
End If
End If
Application.EnableEvents = True
End Subs a l u d o s
