Excel: valor de la última celda rellena con los valores de una lista
Tengo una tabla en Excel en la cual voy registrando el ciclo de revisiones de una serie de documentos. Hay varias columnas intercaladas con la fecha y el estado del documento (entre otras cosas, pero por simplificar pongo sólo estos datos). Por ejemplo Columna A "Nombre del documento", Columna B "Fecha de revisión", Columna C "Estado", las columnas B y C se repiten varias veces.
Quiero aplicar un formato condicional a las celdas de la columna "nombre del documento” para que se coloreen de diferente manera dependiendo del último estado del documento, de tal manera que de una forma visual rápida pueda saber el estado actual de cada documento sin necesidad de buscar a la derecha de la tabla. El estado del documento se elige de una lista de posibles valores (6 en total).
¿Alguien puede darme una solución? He leído varias entradas sobre cómo obtener el valor de la última celda rellena, pero en mi caso, esto no sería válido porque después del último valor de “estado” podría haber algún valor numérico ya que usamos esta misma hoja para hacer cálculos sobre los ciclos de vida de los documentos. Tendría que ser algo que me permitiera elegir el último valor de las columnas “Estado” en la fila del documento que me interese.
Por ejemplo, si un documento que tiene el estado “ENVIADO PA” y está por lo tanto coloreado de un color, digamos por ejemplo verde, y en la siguiente revisión ese documento cambia al estado “APROBADO”, debería colorearse en otro color, digamos por ejemplo azul.
1 Respuesta
H o l a:
Envíame tu archivo con varios ejemplos, reviso cuál sería la mejor alternativa, para que de forma automática te ponga los colores.
También coméntame, aproximadamente cuántos registros llega a tener tu hoja de documentos.
Mi correo [email protected]
En el asunto del correo escribe tu nombre de usuario “Belén Fresnillo” y el título de esta pregunta.
Muchas gracias Dante por tu respuesta, acabo de mandarte un correo con algunos ejemplos.
Un saludo.
H o l a:
Pon la siguiente macro en los eventos de tu hoja:
Private Sub Worksheet_Change(ByVal Target As Range)
'Por.Dante Amor
If Target.Column < Columns("I").Column Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Row < 6 Then Exit Sub
'
cfinal = Cells(5, Columns.Count).End(xlToLeft).Column
Set h = Sheets("Datos listas")
u = h.Range("A" & Rows.Count).End(xlUp).Row
i = Target.Row
For j = cfinal To Columns("I").Column Step -1
If Cells(5, j) Like "ESTADO*" Then
If Cells(i, j) <> "" Then
estado = Cells(i, j)
Set b = h.Range("A2:A" & u).Find(estado, lookat:=xlWhole)
If Not b Is Nothing Then
wcolor = h.Cells(b.Row, "B").Interior.ColorIndex
Else
wcolor = h.Cells(u + 1, "B").Interior.ColorIndex
End If
Exit For
Else
If Cells(i, j - 3) <> "" Then
wcolor = h.Cells(u + 1, "B").Interior.ColorIndex
Exit For
End If
End If
End If
Next
If wcolor <> "" Then
Cells(i, "D").Interior.ColorIndex = wcolor
End If
End SubSigue las Instrucciones para poner la macro en los eventos de worksheet
- Abre tu libro de excel
- Para abrir Vba-macros y poder pegar la macro, Presiona Alt + F11
- Del lado izquierdo dice: VBAProject, abajo dale doble click a worksheet(tu hoja)
- Del lado derecho copia la macro
En la hoja "Datos listas", debes poner la relación de Estado y su color que prefieras:
Al final de los estados debes dejar una fila vacía y poner el color verde, ese color verde, será el que se ponga cuando empiece una revisión.
Cada vez que modifiques una columna desde la columna I en adelante, el color de esa misma fila pero de la columna "D" se actualizará.
Para actualizar todos los colores de cada registro:
1. Pon el cursor en la fila 6 de la columna "I",
2. Presiona la tecla F2 (para editar la celda),
3. Presiona Enter, en automático el color se actualiza
El cursor se pasó a la fila 7, repite los paso 2 y 3 para todas las filas. Los colores quedarán actualizados. Este proceso solamente lo tienes que realizar una vez.
A partir de este momento cualquier cambio que realices se actualizará el color.
H o l a:
Esta es la macro que va en los eventos de tu hoja:
Private Sub Worksheet_Change(ByVal Target As Range)
'Por.Dante Amor
If Target.Column < Columns("I").Column Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Row < 6 Then Exit Sub
'
cfinal = Cells(5, Columns.Count).End(xlToLeft).Column
Set h = Sheets("Datos listas")
u = h.Range("A" & Rows.Count).End(xlUp).Row
i = Target.Row
For j = cfinal To Columns("I").Column Step -1
If Cells(5, j) Like "ESTADO*" Then
If Cells(i, j) <> "" Then
estado = Cells(i, j)
Set b = h.Range("A2:A" & u).Find(estado, lookat:=xlWhole)
If Not b Is Nothing Then
wcolor = h.Cells(b.Row, "B").Interior.ColorIndex
Else
wcolor = h.Cells(u + 1, "B").Interior.ColorIndex
End If
Exit For
Else
If Cells(i, j - 3) <> "" Then
wcolor = h.Cells(u + 1, "B").Interior.ColorIndex
Exit For
End If
End If
End If
Next
If wcolor <> "" Then
Cells(i, "D").Interior.ColorIndex = wcolor
End If
End SubSigue las Instrucciones para poner la macro en los eventos de worksheet
- Abre tu libro de excel
- Para abrir Vba-macros y poder pegar la macro, Presiona Alt + F11
- Del lado izquierdo dice: VBAProject, abajo dale doble click a worksheet(tu hoja)
- Del lado derecho copia la macro
En la columna "D" ya no hay formato condicional.
En la hoja "Datos listas", debes poner la relación del estado y su color:
Debes dejar una fila en blanco y poner el color verde; el color verde se ocupará para cuando inicies con una Revisión.
Para actualizar todos los registros de tu hoja: "STATUS", realiza lo siguiente:
1. Selecciona la celda "I6"
2. Presiona la tecla "F2" (para editar la celda)
3. Presiona la tecla Enter (para salir de la edición)
4. En automático el color se actualiza, repite los pasos 2 y 3 para cada uno de los registros que tienes en tu hoja. Este proceso solamente lo tienes que realizar una vez.
5. Después de actualizar los colores, cada que modifiques alguna de las columnas de la columna "I" en adelante, el color de la fila que modificaste se actualizará.
