Using Excel VB to change imported data from Access colour coded accordingly -

-

what i'm trying change specific range cells specific colours. works when there increase , when more -2.00% should red. however, when decreasing previous time should green, , once below -2.00% should go black again.

so cell data in starts @ c2 , ends @ h54. works in row format c2 main d2 continue data, etc. c3 new main data , d3 continue of data, etc.

my code have been testing not getting right follows:

range("c2").select if range("c2").value >= "-2.00%" selection.font .color = -16776961 .tintandshade = 0 end elseif range("c2").value < "-2.00%" selection.font .themecolor = xlthemecolorlight1 .tintandshade = 0 end end if  range("d2").select if range("d2").value <= "-2.00%" & range("c2").value selection.font .color = -11489280 .tintandshade = 0 end elseif range("d2").value > "-2.00%" & range("c2").value selection.font .color = -16776961 .tintandshade = 0 end elseif range("d2").value < "-2.00%" selection.font .themecolor = xlthemecolorlight1 .tintandshade = 0 end end if  range("e2").select if range("e2").value <= "-2.00%" & range("d2").value selection.font .color = -11489280 .tintandshade = 0 end elseif range("e2").value > "-2.00%" & range("d2").value selection.font .color = -16776961 .tintandshade = 0 end elseif range("e2").value < "-2.00%" selection.font .themecolor = xlthemecolorlight1 .tintandshade = 0 end end if

however, when goes below 2.00% still green, , same error stays when increase again...

i appreciate assistance in getting done asap... if know of shorter method please put down me test out. thank taking time review this.

here sample picture of result , should be: enter image description here

this seems follow business logic perceive code , sample image(s).

sub ject()     dim r long, c long, vrtrns variant, thrshld double      thrshld = 0.02      worksheets("sheet2")         .cells(1, 1).currentregion             .resize(.rows.count - 1, .columns.count - 2).offset(1, 2)                 .cells.font.colorindex = xlcolorindexautomatic                 vrtrns = .value2                 r = lbound(vrtrns, 1) ubound(vrtrns, 1)                      'deal first value                     if vrtrns(r, lbound(vrtrns, 2)) >= thrshld                         .cells(r, 1).font.color = vbred                     end if                      'the remainder of columns in row                     c = lbound(vrtrns, 2) + 1 ubound(vrtrns, 2)                         select case vrtrns(r, c)                             case >= thrshld                                 .cells(r, c).font.color = _                                     iif(vrtrns(r, c) >= vrtrns(r, c - 1), vbred, vbgreen)                             case < thrshld                                 .cells(r, c).font.colorindex = xlcolorindexautomatic                         end select                     next c                 next r             end         end     end end sub

results:

    returns_business_logic


Comments

Post a Comment

Popular posts from this blog

java - Run spring boot application error: Cannot instantiate interface org.springframework.context.ApplicationListener -

-
i have spring project , try make use spring boot , run @ embadded tomcat following : https://spring.io/guides/gs/rest-service/ this application //@configuration //@enableaspectjautoproxy @springbootapplication @componentscan(basepackages = "gux.prome") public class application { public static void main(string[] args) { springapplication.run(application.class, args); } } if use maven command: mvn spring-boot:run , project starts fine, need debug , run main method @ intelij, exception occurs: exception in thread "main" java.lang.illegalargumentexception: cannot instantiate interface org.springframework.context.applicationlistener : org.springframework.boot.logging.classpathloggingapplicationlistener @ org.springframework.boot.springapplication.createspringfactoriesinstances(springapplication.java:414) @ org.springframework.boot.springapplication.getspringfactoriesinstances(springapplication.java:394) @ org.springframework.bo...
Read more

python - pip wont install .WHL files -

-
when try install .whl gohike typing cmd c:\users\owner\downloads>pip install ta_lib-0.4.9-cp27-none-win_amd64.whl i print out: `processing c:\users\owner\downloads\ta_lib-0.4.9-cp27-none-win_amd64.whl complete output command python setup.py egg_info: traceback <most recent call last>: file "<string>", line 1, in <module> ioerror: [errno 2] no such file or directory:'c:\\users\\owner\\appdata\\lo cal\\temp\\pip-9gwk2c-build\\setup.py' ----------------------------------------- command "python setup.py egg_info" failed error code 1 in c:\users\owner\ap pdata\local\temp\pip-9gwk2c-build\` the part between pip- , -build in pip-9gwk2c-build different on same file. i asked python 2.7.11 pip 8.0.3 can't install gohike whl of ta-lib 0.4.9 few days ago , decided ask differently. can me! you have outdated pip not understand wheels. wheels not supposed have setup.py inside. try upgrading pip pip in...
Read more

Excel VBA "Microsoft Windows Common Controls 6.0 (SP6)" Location Changes -

-
i have excel workbook used generate quotations customers. there main page user inputs data, , there buttons generate various pdfs , send them customer. however, 1 user consistently getting error preventing generating , sending pdfs. i beleive "microsoft windows common controls 6.0 (sp6)". it's installed on users' computers. on mine, location "c:\windows\syswow64\mscomctl.ocx" on others "c:\windows\system32\mscomctl.ocx" the user experiencing issues on 32-bit machine, "mscomctl.ocx" file should in system32. have manually put in there, , pointed common controls reference in vba it. issue seems keep reverting looking in syswow64. since folder doesn't exist, can't find it, , can't produce pdfs. i thought perhaps issue user running older version of office (2007) there users on 2013 , 2016. thought perhaps mixture of 64-bit , 32-bit users. user gets sent workbook other users (as quotation process goes further, gets passed ...
Read more