excel - copy cell background color and past it corresponding cell of another sheet -


i have been trying write macro copy cell background color , past corresponding cell of sheet.i have lots of values on sheet 1 , gave background color using conditional formatting ,after want copy color , past corresponding cell of sheet 2 without pasting value.example if sheet 1 cell a1 has red color specific value ,i want transfer color sheet 2 a1. giving picture of excel sheet 1 , code.enter image description here 

    sub copycolor()     dim introw integer     dim rngcopy range     dim rngpaste range        introw = 1 20           set rngcopy = sheet1.range("a" & introw + 0)         set rngpaste = sheet2.range("b" & introw)          'test see if rows 500+ have value         if rngcopy.value <> ""              'since has value, copy value , color             rngpaste.value = rngcopy.value             rngpaste.interior.color = rngcopy.interior.color           end if     next introw end sub

i use conditional formatting giving color , here use 2 color.one red , 1 white.red use higher value , white lower vaue. nice if me out problem. 

rngpaste.interior.color = rngcopy.displayformat.interior.color

seems work me. keep in mind displayformat read-only , not allowed return value outside of function it's used in. available in excel 2010 +

i editing answer include other stuff mentioned , realized getting confusing explain in separate chunks. here's recommended approach achieve you're saying. 

public sub copycolor() dim sourcesht worksheet dim targetsht worksheet dim rngcopy range dim rngpaste range dim lastcopyrow long dim lastcopycolumn long  'define our source sheet , target sheet set sourcesht = thisworkbook.worksheets("sheet1") set targetsht = thisworkbook.worksheets("sheet2")  'find our used space on source sheet lastcopyrow = sourcesht.cells(rows.count, "a").end(xlup).row lastcopycolumn = sourcesht.cells(1, columns.count).end(xltoleft).column  'setup our ranges can sure don't loop through unused space set rngcopy = sourcesht.range("a1:" & sourcesht.cells(lastcopyrow, lastcopycolumn).address) set rngpaste = targetsht.range("a1:" & targetsht.cells(lastcopyrow, lastcopycolumn).address)  'loop through each row of each column. ' go through each cell in column 1, move on column 2 col = 1 lastcopycolumn     cel = 1 lastcopyrow         ' if string value of our current cell not empty.         if rngcopy.cells(cel, col).value <> ""             'copy source cell displayed color , paste in target cell             rngpaste.cells(cel, col).interior.color = rngcopy.cells(cel, col).displayformat.interior.color         end if     next cel next col end sub

Comments

Post a Comment

Popular posts from this blog

php - failed to open stream: HTTP request failed! HTTP/1.0 400 Bad Request -

this code i'm using find latitude , longitude of place: function coords($address){ echo $url="http://maps.googleapis.com/maps/api/geocode/json?address=".$address; $json = file_get_contents(urlencode($url)); $data = json_decode($json, true); print_r($data['results'][0]['geometry']['location']['lat']); print_r($data['results'][0]['geometry']['location']['lng']); } however returns warning: failed open stream: http request failed! http/1.0 400 bad request if use above code in simple procedure works fine not in function. note: have tried curl_init()... method produced same result? instead of encoding whole url encode address part. following code work fine $add='jamshoro phase 2'; $url="http://maps.googleapis.com/maps/api/geocode/json?address=".urlencode($add); $json = file_get_contents($url); $data = json_decode($json, true); print_r($data['r
Read more

java - How to filter a backspace keyboard input -

i have code gives message when key number pressed: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochararray().length - 1]; if (!(ch >= '0' && ch <= '9')) { system.out.println("the char entered not number"); t.consume(); } } }); now if press on wrong button , press backspace remove it, error message. how can add backspace input if statement? i have found answer: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochar
Read more

java - Show Soft Keyboard when EditText Appears -

i have code in android app, alert dialog , keyboard, , when dialog shown want keyboard appear. not , not sure why? here code: final alertdialog.builder alert = new alertdialog.builder(this); alert.settitle("title"); alert.setmessage("message"); final edittext input = new edittext(this); input.setinputtype(inputtype.type_class_number); alert.setview(input); input.setonfocuschangelistener(new view.onfocuschangelistener() { @override public void onfocuschange(view v, boolean hasfocus) { if (hasfocus) { log.i(tag, "in focus"); inputmethodmanager inputmethodmanager = (inputmethodmanager) getsystemservice(context.input_method_service); inputmethodmanager.showsoftinput(input, inputmethodmanager.show_implicit); } } }); but keyboard not appear though edit text in focus, , tell appear why, doesn't come up? thanks in advance.
Read more