Cell Bar chart (Conditional Formatting or any other way) for Leave Planner

[Juniad]

Hi Experts:
Kindly see the attached sample file where I would like to show cell bar chart based on days falling in the mentioned month.

 

[vletm]

Juniad
You could see those bars eg like in this sample by refreshing with [ ReFresh ]-button.
Notes:
> Cell F1 date is still manually and it should be the 1st day of month ... as well as other dates too.
>I left Your other calculations as those are.
> If From-date is after To-date then bar will be missing and those dates are marked with red.
> I tested only with that sample data.

 

[p45cal]

In your sample file the only difference in the conditional formatting is whether the bar diplays left-to-right or right-to-left. However, this is a cell by cell choice so you can't use a single conditional format for the whole range.
In the attached there's a macro that, cell by cell:

• removes conditional formatting
• adds conditional formatting
• if there's value in the cell to the right it displays the conditional formatting right to left.

Adding lots of conditional formats in this way can get resource-hungry which is why I've removed conditional formatting from cells that don't need it.
I've taken a few shortcuts; leaving most aspects of the conditional formatting as their default, using 30 as a max (which means that even if the whole of February is leave, the bar won't quite fill the cell (tweakable to make it fill).

Button at cell L1 to update the conditional formatting.

The reason one of your bars filled the cell was because you had conditional formatting for that cell alone.
You have some odd values, eg. in cell F6 which shows 31 instead of instead of 12 or 13. The conditional format I've added depends on the value in the cell, like yours.

code:

Sub blah()
For Each cll In Range("F2:K11").Cells    'adjust yourself, works on the active sheet.
  'For Each cll In Range("F18:K27").Cells
  cll.FormatConditions.Delete
  If Len(cll.Value) > 0 Then
    With cll.FormatConditions.AddDatabar
      .MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
      .MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=30
      .BarFillType = xlDataBarFillSolid
      If Len(cll.Offset(, 1).Value) > 0 Then .Direction = xlRTL    ' else .Direction = xlLTR
    End With
  End If
Next cll
End Sub

 

[p45cal]

'Thank you' wouldn't go amiss.