Require flexible conditional formatting

[DespoExcel]

HI,

I need to standardised a formula for conditional formatting using data bar for the salary column.
Take for example,
Where Qualification = DPL (L2), Salary Range : 2800 - 3500, so where my salary, i.e. 3500 will show a data bar ( max )
Where Qualification = DPT, Salary Range : 2500 - 3500, so where my salary, i.e. 3000 will show a data bar ( in the middle )
Where Qualification = DPT (L2), Salary Range : 2000 - 3000, so where my salary, i.e. 2000 will show a data bar ( EMPTY )
Where Qualification = DPT (L1), Salary Range : 1500 - 2500, so where my salary, i.e. 2200 will show a data bar ( close to max )
Where Qualification = Basic, Salary Range : 1000 - 2250, so where my salary, i.e. 1200 will show a data bar ( close to min )
Where Qualification = CIT, Salary Range : 1250 - 1750, so where my salary, i.e. 1500 will show a data bar ( in the middle )

I want to make this conditional formatting flexible such that when i select a particular qualification, the salary range will change accordingly.
And then the salary data bar will then change too.

I have already attached a sample workbook. Please help! Need this urgently SAMPLE HELP CONDITIONAL FORMATTING.xlsx

[misrasomendra]

See the file, Rule applied to F2 and F3, repeat the same for all the cells.

[kvsrinivasamurthy]

Pl see the attached file.
Worksheet_change event is used

[Vikas_Gautam]

Here is the code for Fast Conditional formatting as you demanded..

Please Login or Register  to view this content.

Just a recorded macro with some changes...
Check the attached file and tell is that what your were trying to achieve..

[DespoExcel]

Pl see the attached file.
Worksheet_change event is used

I tried. but couldn't see the data bar. the value is intact.
am i suppose to do anything else besides copy and paste and modify the code & conditional formatting ( insert formula )

[DespoExcel]

Here is the code for Fast Conditional formatting as you demanded..

Please Login or Register  to view this content.

Just a recorded macro with some changes...
Check the attached file and tell is that what your were trying to achieve..

I tried. but couldn't see the data bar. the value is intact.
am i suppose to do anything else besides copy and paste and modify the code & conditional formatting ( insert formula )

[Vikas_Gautam]

You have to just copy and paste the code and run the code...
and the other thing to have to ensure is that the Original file layout must match one that you provided in the example file
OTHERWISE we have to change our code accordingly...

thanks..

Regards,

[DespoExcel]

You have to just copy and paste the code and run the code...
and the other thing to have to ensure is that the Original file layout must match one that you provided in the example file
OTHERWISE we have to change our code accordingly...

thanks..

Regards,

Hi what do you mean by the difference in layout?

i edited to fit mine:

Sub ConFormat()
For r = 2 To Cells(Rows.Count, "M").End(xlUp).Row
Cells(r, "M").Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueFormula, newvalue:= _
"=INDEX(Sheet7!$E$19:$E$24,MATCH(Sheet4!$J$" & r & ",Sheet7!$D$19:$D$24,0))"
.MaxPoint.Modify newtype:=xlConditionValueFormula, newvalue:= _
"=INDEX(Sheet7!$F$19:$F$24,MATCH(Sheet4!$J$" & r & ",Sheet7!$D$19:$D$24,0))"
End With
With Selection.FormatConditions(1).BarColor
.Color = 13012579
.TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillSolid
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
Next
End Sub


However, it doesn't work.

[Vikas_Gautam]

Can you post your original file...?
I think this needs personalized implementation...


Regards,

[DespoExcel]

SAMPLE CONDITIONAL FORMATTING ( MORE REAL).xlsx

Here it is. I have placed in the exact position. So this will do :D
pls do help! thanks

[kvsrinivasamurthy]

Pl see attached file

[DespoExcel]

Pl see attached file

It says subscript out of range in the following highlighted area when i debugged it.


Sub Macro2()
'
' Macro2 Macro

Sheets("Sheet4").Select
Range("M2").Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueLowestValue
.MaxPoint.Modify newtype:=xlConditionValueHighestValue
End With
With Selection.FormatConditions(1).BarColor
.Color = 8700771
.TintAndShade = 0
End With
End Sub

[DespoExcel]

Pl see attached file

It also says that i can't execute code in break mode. What is this suppose to mean?

[kvsrinivasamurthy]

Do not use macros .Delete them. worksheet_Change event is used for conditional formatting . when change is done in J column F column cell will be conditionally formatted.

[DespoExcel]

Do not use macros .Delete them. worksheet_Change event is used for conditional formatting . when change is done in J column F column cell will be conditionally formatted.

i have done as you have instructed.
But the data bar is not reflected because there is no conditional formatting rules. ( i've compared to the file you have sent )

[kvsrinivasamurthy]

Pl upload your file having problem.