Color de CELDAS segun condicion
Hola! Como haria con VBA, para que (segun lo que escriba en mi textbox1) UNICAMENTE las celdas en el rango "b3:n3" que tengan el valor del textbox1 al pulsar un commandbutton1 esas mismas celdas de se vuelvan de color azul???
intente usar:
.
Private Sub CommandButton1_Click()
On Error Resume Next
Application.DisplayAlerts = False
Sheets("programa").Range("b3:n40").Select
If Not Selection.Value = TextBox1.Value Then
With Selection.Interior
.ColorIndex = 7
.Pattern = xlSolid
End With
End If
If Selection.Value = TextBox1.Value Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
End sub
.
Pero no me funciono, esta me llena todo el rango del mismo color y yo solo kiero que sean las que tengan el valor del textbox1
intente usar:
.
Private Sub CommandButton1_Click()
On Error Resume Next
Application.DisplayAlerts = False
Sheets("programa").Range("b3:n40").Select
If Not Selection.Value = TextBox1.Value Then
With Selection.Interior
.ColorIndex = 7
.Pattern = xlSolid
End With
End If
If Selection.Value = TextBox1.Value Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
End sub
.
Pero no me funciono, esta me llena todo el rango del mismo color y yo solo kiero que sean las que tengan el valor del textbox1
2 respuestas
Respuesta de vorkosigan
1
Prueba esto:
Private Sub CommandButton1_Click()
Dim CeldaInicio, CeldaFin, rango As String
CeldaInicio = "B3"
CeldaFin = "N3"
rango = CeldaInicio & ":" & CeldaFin
For Each cell In Range(rango)
If cell.Value = TextBox1.Value Then
With cell.Interior
.ColorIndex = 5
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
Next
End Sub
Private Sub CommandButton1_Click()
Dim CeldaInicio, CeldaFin, rango As String
CeldaInicio = "B3"
CeldaFin = "N3"
rango = CeldaInicio & ":" & CeldaFin
For Each cell In Range(rango)
If cell.Value = TextBox1.Value Then
With cell.Interior
.ColorIndex = 5
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
Next
End Sub
Respuesta de antares18
1
La verdad me ha costao pero lo he conseguido
'BOTÓN
Private Sub CommandButton1_Click()
Dim rango As Range
Set rango = Range("B3:N40") '--->El rango que quieres
Call color_azul(rango)
End Sub
SUB
Private Sub color_azul(rango As Range)
Dim i As Integer
Dim j As Integer
i = rango.Row
j = rango.Column
For i = rango.Row To (rango.Row + rango.Rows.Count) - 1
For j = rango.Column To (rango.Column + rango.Columns.Count) - 1
If (CStr(ActiveSheet.Cells(i, j)) = TextBox1.Text) Then --->Textbox
'Si en vez del textbox es una celda pon
' If (CStr(ActiveSheet.Cells(i, j)) = Range("A1") Then
'Lo compara con lo que hay en la celda A1
ActiveSheet.Cells(i, j).Interior.ColorIndex = 5 'Azul --->color
ActiveSheet.Cells(i, j).Interior.Pattern = xlSolid
Else
ActiveSheet.Cells(i, j).Interior.ColorIndex = 6 'amarillo --->color
ActiveSheet.Cells(i, j).Interior.Pattern = xlSolid
End If
Next j
Next i
End Sub
'BOTÓN
Private Sub CommandButton1_Click()
Dim rango As Range
Set rango = Range("B3:N40") '--->El rango que quieres
Call color_azul(rango)
End Sub
SUB
Private Sub color_azul(rango As Range)
Dim i As Integer
Dim j As Integer
i = rango.Row
j = rango.Column
For i = rango.Row To (rango.Row + rango.Rows.Count) - 1
For j = rango.Column To (rango.Column + rango.Columns.Count) - 1
If (CStr(ActiveSheet.Cells(i, j)) = TextBox1.Text) Then --->Textbox
'Si en vez del textbox es una celda pon
' If (CStr(ActiveSheet.Cells(i, j)) = Range("A1") Then
'Lo compara con lo que hay en la celda A1
ActiveSheet.Cells(i, j).Interior.ColorIndex = 5 'Azul --->color
ActiveSheet.Cells(i, j).Interior.Pattern = xlSolid
Else
ActiveSheet.Cells(i, j).Interior.ColorIndex = 6 'amarillo --->color
ActiveSheet.Cells(i, j).Interior.Pattern = xlSolid
End If
Next j
Next i
End Sub