excel - según - formato condicional de una celda dependiendo del valor de otra

Establecer el color de una celda según el color de otra celda

Question

Lo que me gustaría tener es:

IF A1 in Sheet 2 is blue
Then A1 in Sheet 1 changes to blue

Sé que puedo obtener el color de A1 en la Hoja 2 usando:

=GET.CELL(63,Sheet2!A1)

( Excel: ¿Puedo crear una fórmula condicional basada en el color de una celda? )

Pero no puedo entender qué debería hacer en el siguiente paso.

¿Alguna sugerencia?

#

Actualización el 12.01.2015 lo siento si di muy poca información sobre mi caso, y no señalé claramente al principio si quiero hacerlo en VBA o no. Al principio pensé que una función funcionaría, pero al considerar mi archivo, una función podría no funcionar en absoluto.

Se trata de la salida de un análisis de correlación de SPSS, hay tres columnas: coeficiente de correlación, valor de p y tamaño de muestra. Necesito verificar el coeficiente y el valor p al mismo tiempo, y presentar el coeficiente de una manera legible. Digamos que ejecuto una correlación entre 50 variables con 100 variables, no pegaría el coeficiente y el valor p en una hoja, sino que:

hoja uno: coeficiente hoja dos: valor p

Lo que quiero tener es:

Si el valor del valor p es mayor que 0.05, entonces el coeficiente (celda) cambia a azul / azul oscuro o negro.

De modo que cuando miro la primera hoja, sé que los azules deben ignorarse por no ser significativos.

Voy a probar todas las sugerencias a continuación e informarlo más tarde.

Answer 1

La función GET.CELL, aunque es útil, proviene del antiguo lenguaje de macros XLM que se usaba antes de VBA. Puede encontrar restricciones usando esto, por ejemplo, en ese momento Excel usó una cantidad limitada de colores (¿he leído alrededor de 60?).

Alternativamente, con un poco de VBA, podría experimentar con el objeto interior y también el objeto fuente :

Sheets("Sheet1").Range("A1").Interior.Color = vbBlue
Sheets("Sheet1").Range("A1").Font.Color = vbYellow
If Sheets("Sheet1").Range("A1").Interior.Color = vbBlue Then _
Sheets("Sheet2").Range("A1").Interior.Color = vbBlue
If Sheets("Sheet1").Range("A1").Font.Color = vbYellow Then _
Sheets("Sheet2").Range("A1").Font.Color = vbYellow

Es probable que necesite explorar las diversas formas de especificar los colores que se utilizarán para brindarle el máximo control / flexibilidad.

Answer 2

Solo para ser claros y mantener la funcionalidad que entregas simple, podrías usar el formato condicional y elegir establecer el formato con un color. Esto es increíblemente fácil una vez que sepa cómo. El truco principal es qué fórmula introducir y específicamente qué celda necesita una fórmula de formatos condicional para referenciar cuando el formato condicional se aplica a un rango de múltiples celdas.

Como ejemplo. Si su regla de formato condicional se crea de manera que se aplique al rango $ C $ 5: $ C $ 10, la fórmula que use a menudo deberá ingresarse como = (A5 = "A"). Tenga en cuenta que esta es una fórmula de direccionamiento relativo, es decir, sin signos de dólar. esto tiene el efecto de que la celda c6 inspecciona el valor de a6, etc.

Su única complicación ahora es inspeccionar el formato de la celda en lugar del valor que almacena. En 2013, todavía puede usar =GET.CELL(63,A5) para hacer esto, sin embargo, esto no se puede ingresar en la fórmula de la regla de CF ... Otros mensajes discuten los por qué y las causas de usar esto. Vea este enlace que describe cómo obtener información de la celda.

Entonces terminarás con una fórmula en una celda al lado de la celda que tiene el color. La fórmula usará un rango con nombre que devuelve verdadero o falso dependiendo de si el color de la celda coincide con el color especificado en el rango especificado. El formato condicional en otra hoja hará referencia a esta celda de fórmula y establecerá el color de la nueva celda.

Utilizaría la siguiente fórmula en el rango llamado "Get. = GET.CELL (65, OFFSET (INDIRECT (" RC ", FALSE), 0,1))

Tengo esto para trabajar, y la información clave se puede encontrar en la página del sitio web referenciada.

¿Okay?

Answer 3

No recomendado debido a la dependencia de la función de macro XLM (no XML) GET.CELL . Esto desde una tecnología introducida hace 30 años que fue reemplazada de manera efectiva ocho años después. Con casi todos sus elementos ahora desaparecidos, se puede esperar que los pocos que quedan tengan una baja expectativa de vida. Microsoft alienta la migración a VBA.

Sin embargo, ha preguntado "cómo" en lugar de "por qué no", así que le sugiero que proceda desde donde ha llegado y seleccione Sheet1 A1 y HOME> Estilos - Formato condicional - Nueva regla ..., use una fórmula para determinar qué celdas para formatear , formatear valores donde esta fórmula es verdadera:

=CellColor=23 

y seleccione el formato azul de su elección, OK, OK, Aplicar.

23 es un número bastante estándar para Azul (no Light, not Dark) pero su configuración puede esperar un número diferente.

Tenga en cuenta que otra desventaja es que, a diferencia de CF en general, la respuesta no es automática: es posible que deba ingresar algo en la Hoja A1, o Mayús + F9 para forzar una actualización.

Si sus datos están distribuidos en dos hojas (Sheet1 y Sheet2, ambas ColumnA) y hay una relación 1: 1 (el valor p en A1 de Sheet2 es el del coeficiente de correlación en A1 de Sheet1), entonces una regla de formato condicional simple puede ser suficiente:

Seleccione Sheet1 ColumnA y HOME> Styles - Formato condicional, nueva regla ...

Use una fórmula para determinar qué celdas formatear
Formatee los valores donde esta fórmula es verdadera:

=Sheet2!A1>0.05

Formato ... , seleccione azul oscuro o para adaptarse, OK, OK.

La misma regla se puede aplicar en la Hoja2 (Columna A) de la misma manera, por lo que las celdas (por fila) condicionalmente formateadas en una hoja son las formateadas condicionalmente en la otra.

Answer 4

Lo que necesita es una forma de detectar cambios en el formato de celda . Parece que no hay ningún evento que se desencadena al cambiar el formato. Consulte Cómo detectar cambios en el formato de celda?

Describiré una solución , casi paso a paso. No es una pulsación de tecla por pulsación de tecla, por lo que es posible que tenga que googlear un poco, según su conocimiento de fondo. La descripción no es corta, así que por favor léela.

Tienes que:

1. Detectar cambios en la selección (hay un evento para esto).
2. Investigue sobre el color de su celda fuente.
3. Actúa si es necesario.

Vaya al Editor de Visual Basic (VBE) y agregue el código en tres módulos:

1. Un módulo estándar (por ejemplo, Módulo1). Primero debe insertar el módulo.
2. ThisWorkbook.
3. Sheet2.

En Module1:

Public prev_sel As Range
Public wssrc As Worksheet, wstrg As Worksheet
Public ssrc As String, strg As String
Public rngsrc As Range, rngtrg As Range
Sub copy_color(rngs As Range, rngt As Range)
Dim csrc As Long
csrc = rngs.Interior.Color
If (csrc = vbBlue) Then
rngt.Interior.Color = vbBlue
End If
End Sub
Sub copy_color2(rngs As Range, rngt As Range)
If (TypeName(prev_sel) = "Range") Then
Dim pss As String
pss = prev_sel.Parent.Name
If (pss = ssrc) Then
Dim ints As Range
Set ints = Application.Intersect(rngs, prev_sel)
If (Not (ints Is Nothing)) Then
Call copy_color(rngs, rngt)
End If
End If
End If
End Sub

En ThisWorkbook:

Private Sub Workbook_Open()
ssrc = "Sheet2"
strg = "Sheet1"
Set wssrc = Worksheets(ssrc)
Set wstrg = Worksheets(strg)
Set rngsrc = wssrc.Range("A1")
Set rngtrg = wstrg.Range("A1")
Call copy_color(rngsrc, rngtrg)
If (TypeName(Selection) = "Range") Then
Set prev_sel = Selection
Else
Set prev_sel = Nothing
End If
End Sub

En Sheet2:

Private Sub Worksheet_Deactivate()
Call copy_color(rngsrc, rngtrg)
If (TypeName(Selection) = "Range") Then
Set prev_sel = Selection
Else
Set prev_sel = Nothing
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call copy_color2(rngsrc, rngtrg)
If (TypeName(Target) = "Range") Then
Set prev_sel = Target
End If
End Sub

Pronto editaré con explicaciones. Sin embargo, al leer cuidadosamente, se puede entender fácilmente.

Notas:

1. Este código no actúa si el color de la celda fuente cambia de vbBlue a otra cosa. No especificaste nada para esta acción. En realidad, su especificación no fue lo suficientemente detallada como para cubrir todos los casos posibles.

2. Puede haber casos (muy poco probable, creo) donde este código falla. Por ejemplo, si el color se cambia a través de otro código VBA, sin seleccionar / deseleccionar celdas.

3. La idea es verificar la necesidad de actuar después de tantos eventos relevantes como sea posible. Aquí estoy detectando Workbook_Open , Worksheet_Deactivate , Worksheet_SelectionChange . Puede agregar otros eventos con Sub s adecuados, por ejemplo, Workbook_BeforeClose , Workbook_BeforeSave . Todo esto es una forma de sustituir el evento no existente de cambio de formato de celda.

4. Me gusta la respuesta por nueces (aunque no tuve tiempo para probarla). Pero el presente da una flexibilidad que no está disponible con el otro. Puede haber algunos casos (dependiendo de lo que necesite hacer) que no estén cubiertos por este.

5. Hay otras combinaciones posibles de lugares para ubicar la declaración de variables y otro código, esencialmente realizando las mismas acciones.