0

there is already a post concerned this question: Conditional Formatting using Excel VBA code

if i use last routine, it add all necessary format conditions (from 1 to 8). however, when it applies that rules to the proper ranges, using Cells.FormatConditions(1).ModifyAppliesToRange Range("O8:P507"), the rules themselves are adjusted to that ranges rows and columns accordingly. how can i keep the rules in without being adjusted to the destination ranges? O8:O507","O8:P507", "B8:B507" etc... thans in advance for help

Mat257
  • 33
  • 7
  • Can you clarify what you mean perhaps with an example and expected outcome? You can fix the range references with $ e.g. Range("$O$8:$P$507") – QHarr Jan 15 '18 at 08:11
  • hi tahnks for answer. if you click on above thread, you ll be driven to the page where this routine have been posted:Sub ResetFormatting() ' ---------------------------------------------------------------------------------------- ' Written by..: Julius Getz Mørk ' Purpose.....: If conditional formatting ranges are broken it might cause a huge increase.........etc....the code is long – Mat257 Jan 15 '18 at 12:38
  • hi tahnks for answer. if you click on above thread, you ll be driven to the page where this routine have been posted:Sub ResetFormatting() ' ------------------------------------------------------------------------ ' Written by..: Julius Getz Mørk' Purpose..........: If conditional formatting ranges are broken it might cause a huge increase.........etc....the code is long. this routine work fine until i reach lines where it applies the rules to the involved ranges (O8:O507","O8:P507", "B8:B507"). at this stage excel adjust the rules ranges in accordace to the ranges where they are applied – Mat257 Jan 15 '18 at 12:46
  • please can someone help me? thanks – Mat257 Jan 16 '18 at 10:16
  • I was unable to understand the question. Could you have a go at editing the question to demonstrate the problem and the desired outcome versus the actual? – QHarr Jan 16 '18 at 10:18
  • hi, thanks for reply and patience. – Mat257 Jan 17 '18 at 08:05
  • hi, thanks for reply and patience. in the same spreadsheet range (let's say A1:A10) i have to insert more than three rules.thus i need to use, for the same range, three times with-end with. when i need to set cell format to be applied if th rule is true, i need to specify the condition involved, using formatconditions(#).font.colorindex = 3 + formatconditions(#).font.bold=true. hovere vba doesn't understand which rules and i specify and the condition involved (it changes format n.1 insteas n.#). then i tried to use the code posted by Julius Getz Mørk (you can find by clicking above thread) – Mat257 Jan 17 '18 at 08:17
  • it set several conditions and applies , at first istance, in cells(1,1). then ,it specify the ranges where they have to be applied by the command – Mat257 Jan 17 '18 at 08:19
  • it set several conditions and applies , at first istance, in cells(1,1). then ,it specify the ranges where they have to be applied by the command ....Cells.FormatConditions(1).ModifyAppliesToRange. the probles rises at this stage because vba adjust even the rules themselves to the spcified ranges. thus if before the rules was =A1>1, after applies ranges command to rabge (A10:A20), it adjust the rule to = A10>1. actually my purpose is very simple:1) delete all existing c.format rules 2) add severals rules many of them overlap in same ranges (point where issues rise out) – Mat257 Jan 17 '18 at 08:26
  • continued......by using the command formatconditions(#), where (#) stand for rules involved in setting – Mat257 Jan 17 '18 at 08:26
  • i hope having clarified, thanks again – Mat257 Jan 17 '18 at 08:27
  • Please [edit] clarification text to the question so others can easily find it. – QHarr Jan 17 '18 at 08:48

0 Answers0