VBA for conditional formatting

[afrobea_r]

Dear Excel Community,

I currently have a sheet with data resembling this:

Plan	Days
Intended	13
Unintended	12
Intended	14
Intended	15
Intended	16
Unintended	8

<colgroup><col><col></colgroup><tbody>
</tbody>

As the conditional formatting function does not allow application of thick coloured borders, what kind of macro can I use to dynamically apply a thick red border to those cells in the days column only if their corresponding cell value in the plan column is 'unintended'? It must be dynamic as the row data may increase from time to time and certain values within existing rows will also change over time depending on info I have.

I have been stuck for some time. Any help is appreciated!! Thank you in advance!

Best Regards,
stuck

 

[Fluff]

How about

Sub AddBorders()
   Dim Rng As Range
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Replace "Unintended", "=XXX", xlWhole, , False, , False, False
      For Each Rng In .SpecialCells(xlFormulas, xlErrors).Areas
         Rng.Resize(, 2).Borders.Weight = xlThick
         Rng.Resize(, 2).Borders.Color = 45678
      Next Rng
      .Replace "=XXX", "Unintended", xlWhole, , False, , False, False
   End With
End Sub

 

[afrobea_r]

This is absolutely great sir! Thank u for this! As an additional interface that I just came across, how do I input a formula to cater for a situation whereby there are multiple columns like this:

Plan	Days	Plan	Days	Plan	Days	Plan
Intended	6	6	6	6	6	6
Unintended	10	10		10	10	10
Intended	18			18	18	18
Intended	4			4	4	4
Intended	9	9	9
Unintended	14	14	14
Intended	19	19	19	19	19	19
Unintended	15	15	15	15	15	15

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

And the border has to only appear for cell with values as well as tied to the conditional "unintended" in the plan column.

Subsequently, to clear the borders to revert to normal, other than recording a macro, is there a smarter way too?

 

[Fluff]

If the values are constants rather than formulae try

Sub AddBorders()
   Dim Rng As Range
   Dim Ar As Areas
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Replace "Unintended", "=XXX", xlWhole, , False, , False, False
      Set Ar = .SpecialCells(xlFormulas, xlErrors).Areas
      .Replace "=XXX", "Unintended", xlWhole, , False, , False, False
   End With
   For Each Rng In Ar
      Rng.Resize(, 7).SpecialCells(xlConstants).Borders.Weight = xlThick
      Rng.Resize(, 7).SpecialCells(xlConstants).Borders.Color = 45678
   Next Rng
End Sub

 

[afrobea_r]

If the values are constants rather than formulae try

Sub AddBorders()
   Dim Rng As Range
   Dim Ar As Areas
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Replace "Unintended", "=XXX", xlWhole, , False, , False, False
      Set Ar = .SpecialCells(xlFormulas, xlErrors).Areas
      .Replace "=XXX", "Unintended", xlWhole, , False, , False, False
   End With
   For Each Rng In Ar
      Rng.Resize(, 7).SpecialCells(xlConstants).Borders.Weight = xlThick
      Rng.Resize(, 7).SpecialCells(xlConstants).Borders.Color = 45678
   Next Rng
End Sub

Again, brilliant sir. And if the cell values are derived by formula rather constants? How can I make it as if it was the formula option in conditional formatting...

 

[Fluff]

Try

Sub AddBorders()
   Dim rng As Range
   Dim Ar As Areas
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Replace "Unintended", "=XXX", xlWhole, , False, , False, False
      Set Ar = .SpecialCells(xlFormulas, xlErrors).Areas
      .Replace "=XXX", "Unintended", xlWhole, , False, , False, False
   End With
   For Each rng In Ar
      With rng
         .Borders.Weight = xlThick
         .Borders.Color = 45678
         .Resize(, 7).SpecialCells(xlFormulas, 1).Borders.Weight = xlThick
         .Resize(, 7).SpecialCells(xlFormulas, 1).Borders.Color = 45678
      End With
   Next rng
End Sub

 

[afrobea_r]

Try

Sub AddBorders()
   Dim rng As Range
   Dim Ar As Areas
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Replace "Unintended", "=XXX", xlWhole, , False, , False, False
      Set Ar = .SpecialCells(xlFormulas, xlErrors).Areas
      .Replace "=XXX", "Unintended", xlWhole, , False, , False, False
   End With
   For Each rng In Ar
      With rng
         .Borders.Weight = xlThick
         .Borders.Color = 45678
         .Resize(, 7).SpecialCells(xlFormulas, 1).Borders.Weight = xlThick
         .Resize(, 7).SpecialCells(xlFormulas, 1).Borders.Color = 45678
      End With
   Next rng
End Sub

Hi Sir, have tried but when the cell values are derived via formula, the formatting does not apply. How do I recognize the derived value as a value and not a formula?

 

[afrobea_r]

Hi Sir, have tried but when the cell values are derived via formula, the formatting does not apply. How do I recognize the derived value as a value and not a formula?

Kindly ignore my previous. The formula works fine. However, while it recognizes numerical values derived from formulas, can it also recognize text values that are in the cells derived from formulas as well? eg concatenate.

 

[afrobea_r]

Kindly ignore my previous. The formula works fine. However, while it recognizes numerical values derived from formulas, can it also recognize text values that are in the cells derived from formulas as well? eg concatenate.

Pardon me once more, I wrongly communicated the last again. What I really meant was that some of the blank cells are derived via a formula and so they are not really blank in that sense. Will the VBA be able to circumvent this and recognize it as a true blank?

 

[Fluff]

The last code I supplied should do that already, although it won't recognise text values.
If you modify it to recognise text values as well as numerical values, then it will pick up any cell with a formula that returns "".
Unfortunately I know of no way round this, other than removing the formulae