0

follow my question:

I have a project in which I need some shapes to receive conditional formatting based on named cell values.

For this I followed the following reasoning:

I recorded a Macro to identify the encoding of the attribute I would like to change, after that I made its change inside the VBA editor by inserting the IF function and informing the desired formatting conditions.

However, when I run this macro the changes are not applied.

When following the execution of the line-by-line code, I noticed that the lines referring to the formatting of the attribute in question were being skipped. So I made a correction to the indentation, but even so the changes are not being applied. Would anyone have any idea how to solve?

    Sub Condiciona_Icon()


' ---------------------------- FORMATAÇÃO CONDICIONAL DE ÍCONE ------------

'
Sheets("Início").Select


    Range("A14").Select
    ActiveSheet.Shapes.Range(Array("IconEtp1")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        If CP_1 = "Atrasado" Then
        .ForeColor.RGB = RGB(255, 0, 0)
        ElseIf CP_1 = "Regular" Then
        .ForeColor.RGB = RGB(146, 208, 80)
        End If
        .Transparency = 0
        .Solid
    End With
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Rogério Alves
  • 1
  • 1
    Is there anything assigned to `CP_1`? Otherwise it will probably fail both the `if` and the `elseif`and skip to `end if` – Christofer Weber May 05 '21 at 15:20
  • You may also want to [avoid select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code – cybernetic.nomad May 05 '21 at 15:26
  • Yes Christofer, these cells are filled in another worksheet in the same workbook. – Rogério Alves May 05 '21 at 15:27
  • cybernetic.nomad I'll try it. – Rogério Alves May 05 '21 at 15:34
  • Are there only two possible values for CP_1 ? Do you need an `Else` for other values? Where is CP_1 declared and assigned a value? – Tim Williams May 05 '21 at 16:01
  • These cells? Unless you tell it that `CP_1` is something specific, excel won't know what that variable is. – Christofer Weber May 05 '21 at 16:03
  • Sorry, I'm not fluent in english. I meant that. – Rogério Alves May 05 '21 at 16:43
  • Yes Tim Williams. CP_1 is declared in the worksheet itself and its value refers to a specific cell which in this case is the cell "E2" of the workshhet "Prazo" on the same woorkbook. – Rogério Alves May 05 '21 at 16:57
  • Guys I managed to solve. The error was in setting the value as friends said above. Follows the corrected code. – Rogério Alves May 05 '21 at 17:45
  • ```Sub Condiciona_Icon() Dim CP1 As String CP1 = Sheets("Prazos").Range("CP_1") Sheets("Início").Select ActiveSheet.Shapes.Range(Array("IconEtp1")).Select With Selection.ShapeRange.Fill .Visible = msoTrue If CP1 = "Atrasado" Then .ForeColor.RGB = RGB(255, 0, 0) ElseIf CP1 = "Regular" Then .ForeColor.RGB = RGB(146, 208, 80) End If .Transparency = 0 .Solid End With Selection.ShapeRange.Line.Visible = msoFalse End Sub``` – Rogério Alves May 05 '21 at 17:54
  • If you want to use named ranges in your code you can reference them like `[namedRange]`. For you, it would be `[CP_1]` – Toddleson May 05 '21 at 18:26

0 Answers0