M\u00e9todo 1: Aplicar formata\u00e7\u00e3o condicional com base em uma condi\u00e7\u00e3o<\/strong><\/span><\/p>\n M\u00e9todo 2: aplicar formata\u00e7\u00e3o condicional com base em m\u00faltiplas condi\u00e7\u00f5es<\/strong><\/span><\/p>\n M\u00e9todo 3: remover todas as regras de formata\u00e7\u00e3o condicional das c\u00e9lulas<\/strong><\/span><\/p>\n Os exemplos a seguir mostram como usar cada m\u00e9todo na pr\u00e1tica com o seguinte conjunto de dados no Excel:<\/span> <\/p>\n Podemos usar a seguinte macro para preencher c\u00e9lulas no intervalo B2:B11<\/strong> que possuem um valor maior que 30 com fundo verde, fonte preta e estilo de texto em negrito:<\/span><\/p>\n Quando executamos esta macro, recebemos a seguinte sa\u00edda:<\/span> <\/p>\n Observe que cada c\u00e9lula no intervalo B2:B11<\/strong> que possui um valor maior que 30 est\u00e1 sujeita \u00e0 formata\u00e7\u00e3o condicional.<\/span><\/p>\n Qualquer c\u00e9lula com valor igual ou inferior a 30 \u00e9 simplesmente deixada de lado.<\/span><\/p>\n Podemos usar a seguinte macro para aplicar formata\u00e7\u00e3o condicional \u00e0s c\u00e9lulas no intervalo A2:A11<\/strong> com base no nome da equipe:<\/span><\/p>\n Quando executamos esta macro, recebemos a seguinte sa\u00edda:<\/span> <\/p>\n Observe que as c\u00e9lulas com os nomes dos times “Mavericks”, “Blazers” e “Celtics” est\u00e3o todas sujeitas a formata\u00e7\u00e3o condicional espec\u00edfica.<\/span><\/p>\n O \u00fanico time com o nome “Lakers” \u00e9 deixado sozinho, pois n\u00e3o especificamos nenhuma regra de formata\u00e7\u00e3o condicional para c\u00e9lulas com esse nome de time.<\/span><\/p>\n Finalmente, podemos usar a seguinte macro para remover todas as regras de formata\u00e7\u00e3o condicional das c\u00e9lulas da planilha atual:<\/span><\/span><\/p>\n Quando executamos esta macro, recebemos a seguinte sa\u00edda:<\/span> <\/p>\n Observe que toda a formata\u00e7\u00e3o condicional foi removida de cada uma das c\u00e9lulas.<\/span><\/p>\n Os tutoriais a seguir explicam como realizar outras tarefas comuns em VBA:<\/span><\/p>\n VBA: Como contar ocorr\u00eancias de caracteres em uma string<\/a> Voc\u00ea pode usar os seguintes m\u00e9todos em VBA para aplicar formata\u00e7\u00e3o condicional \u00e0s c\u00e9lulas: M\u00e9todo 1: Aplicar formata\u00e7\u00e3o condicional com base em uma condi\u00e7\u00e3o Sub ConditionalFormatOne() Dim rg As Range Dim cond As FormatCondition ‘specify range to apply conditional formatting Set rg = Range(” B2:B11 “) ‘clear any existing conditional formatting rg.FormatConditions.Delete ‘apply conditional formatting […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[],"class_list":["post-4368","post","type-post","status-publish","format-standard","hentry","category-guia"],"yoast_head":"\n Sub<\/span> ConditionalFormatOne()\nDim<\/span> rg As<\/span> Range\nDim<\/span> cond As<\/span> FormatCondition\n\n'specify range to apply conditional formatting\n<\/span>Set<\/span> rg = Range(\" B2:B11<\/span> \")\n\n'clear any existing conditional formatting\n<\/span>rg.FormatConditions.Delete\n\n'apply conditional formatting to any cell in range B2:B11 with value greater than 30\n<\/span>Set<\/span> cond = rg.FormatConditions.Add(xlCellValue, xlGreater, \" =30<\/span> \")\n\n'define conditional formatting to use\n<\/span>With<\/span> cond\n.Interior.Color = vbGreen\n.Font.Color = vbBlack\n.Font.Bold = True<\/span>\nEnd With\n\nEnd Sub\n<\/span><\/strong><\/span><\/pre>\n Sub<\/span> ConditionalFormatMultiple()\nDim<\/span> rg As<\/span> Range\nDim<\/span> cond1 As<\/span> FormatCondition, cond2 As<\/span> FormatCondition, cond3 As<\/span> FormatCondition\n\n'specify range to apply conditional formatting\n<\/span>Set rg = Range(\" A2:A11<\/span> \")\n\n'clear any existing conditional formatting\n<\/span>rg.FormatConditions.Delete\n\n'specify rules for conditional formatting\n<\/span>Set<\/span> cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, \u201c Mavericks<\/span> \u201d)\nSet<\/span> cond2 = rg.FormatConditions.Add(xlCellValue, xlEqual, \u201c Blazers<\/span> \u201d)\nSet<\/span> cond3 = rg.FormatConditions.Add(xlCellValue, xlEqual, \u201c Celtics<\/span> \u201d)\n\n'define conditional formatting to use\n<\/span>With<\/span> cond1\n.Interior.Color = vbBlue\n.Font.Color = vbWhite\n.Font.Italic = True\nEnd With<\/span>\n\nWith<\/span> cond2\n.Interior.Color = vbRed\n.Font.Color = vbWhite\n.Font.Bold = True\nEnd With<\/span>\n\nWith<\/span> cond3\n.Interior.Color = vbGreen\n.Font.Color = vbBlack\nEnd With<\/span>\n\nEnd Sub\n<\/span><\/strong><\/span><\/pre>\n Sub<\/span> RemoveConditionalFormatting()\nActiveSheet.Cells.FormatConditions.Delete\nEnd Sub<\/span><\/strong><\/span><\/pre>\n![\"\"](\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/cv11.jpg\")
<\/p>\n Exemplo 1: Aplicar formata\u00e7\u00e3o condicional com base em uma condi\u00e7\u00e3o<\/strong><\/span><\/h2>\n
Sub<\/span> ConditionalFormatOne()\nDim<\/span> rg As<\/span> Range\nDim<\/span> cond As<\/span> FormatCondition\n\n'specify range to apply conditional formatting\n<\/span>Set<\/span> rg = Range(\" B2:B11<\/span> \")\n\n'clear any existing conditional formatting\n<\/span>rg.FormatConditions.Delete\n\n'apply conditional formatting to any cell in range B2:B11 with value greater than 30\n<\/span>Set<\/span> cond = rg.FormatConditions.Add(xlCellValue, xlGreater, \" =30<\/span> \")\n\n'define conditional formatting to use\n<\/span>With<\/span> cond\n.Interior.Color = vbGreen\n.Font.Color = vbBlack\n.Font.Bold = True\nEnd With<\/span>\n\nEnd Sub<\/span><\/strong><\/span><\/pre>\n![\"\"](\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/cv12.jpg\")
<\/p>\n Exemplo 2: Aplicar formata\u00e7\u00e3o condicional com base em m\u00faltiplas condi\u00e7\u00f5es<\/strong><\/span><\/h2>\n
Sub<\/span> ConditionalFormatMultiple()\nDim<\/span> rg As<\/span> Range\nDim<\/span> cond1 As<\/span> FormatCondition, cond2 As<\/span> FormatCondition, cond3 As<\/span> FormatCondition\n\n'specify range to apply conditional formatting\n<\/span>Set rg = Range(\" A2:A11<\/span> \")\n\n'clear any existing conditional formatting\n<\/span>rg.FormatConditions.Delete\n\n'specify rules for conditional formatting\n<\/span>Set<\/span> cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, \u201c Mavericks<\/span> \u201d)\nSet<\/span> cond2 = rg.FormatConditions.Add(xlCellValue, xlEqual, \u201c Blazers<\/span> \u201d)\nSet<\/span> cond3 = rg.FormatConditions.Add(xlCellValue, xlEqual, \u201c Celtics<\/span> \u201d)\n\n'define conditional formatting to use\n<\/span>With<\/span> cond1\n.Interior.Color = vbBlue\n.Font.Color = vbWhite\n.Font.Italic = True\nEnd With<\/span>\n\nWith<\/span> cond2\n.Interior.Color = vbRed\n.Font.Color = vbWhite\n.Font.Bold = True\nEnd With<\/span>\n\nWith<\/span> cond3\n.Interior.Color = vbGreen\n.Font.Color = vbBlack\nEnd With<\/span>\n\nEnd Sub<\/span><\/strong><\/span><\/pre>\n![\"\"](\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/cv13.jpg\")
<\/p>\n Exemplo 3: Remover todas as regras de formata\u00e7\u00e3o condicional das c\u00e9lulas<\/strong><\/span><\/h2>\n
Sub<\/span> RemoveConditionalFormatting()\nActiveSheet.Cells.FormatConditions.Delete\nEnd Sub<\/span><\/strong><\/span><\/pre>\n<\/p>\n Recursos adicionais<\/strong><\/span><\/h2>\n
VBA: como verificar se uma string cont\u00e9m outra string<\/a>
VBA: uma f\u00f3rmula para \u201cSe\u201d a c\u00e9lula cont\u00e9m\u201d<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"